Dynamic Column: Input default zero value if the column not present

[rohan146]

Is there any mechanism to have as an output certain predefined columns with zero as default value even if the column is not coming as an output?

Let me explain:-

In my workflow, i am classifying transactions on basis of certain properties.

Based on the properties i am classifying the transactions by assigning some keywords as Lables.

Now i am using Cross-Tab to generate new column names as per these labels.

There are about 10 fixed keywords for these labels.

Based on transaction rules, maybe 7 or 8 or 9 or all 10 labels get picked up.

So the Cross-tab generates 7,8,9 or all 10 columns.

Now my requirement is to have a mechanism to also generate these missing 2 or 3 columns and have value as Zero assigned to these missing columns.

This is the table structure:-

Serial No	Name	A	B	C	D	E
1	ABC	10	20	30	40	50
2	DEF	2	4	6	8
3	GHI	1	2	3	4	5
4	JKL	22	33	44	55	66
5	MNO	1	2	3	4

A B C D & E is list of key words.

Now if the transactions are such that Cross-Tab does not generate columns D & E.

Then the requirement is to still generate columns D & E and assign the values as Zero to all records in this column.

[mbarone]

Do you have a mock-up of what the data looks like before the cross tab?  I'm assuming you have your A,B,C,D,E in a column.  You could do something like have a reference table using a text input tool with all the keywords and have a value column with all zeros.  Then union them to the pre-cross tab data stream and summarize with a sum.  But the way you set that up might differ based on what the data looks like pre-cross tab.

[Qiu · Accepted solution]

@rohan146 
As @mbarone suggested, we need your desired table schema defined.

[rohan146]

This is why i absolutely love this Alteryx Community.

Best brains working on Alteryx, ever ready to help out fellow community members with any queries 🙂