how to add values with dynamic columns

[wzhu717]

i have ran into a little challenge, So I have a set of values and I cross tab these values so they becomes the title of the columns, but sometimes there are additional values in the list so when I cross tab them, it creates more columns, then when I put the formula tool in, it is missing those new values. If I add them in the formula tool when it doesn’t happen it will creates an error. Is there a way to put in the formula tool so it recognizes the new created columns? 

[TomWelgemoed]

Hi @wzhu717 ,

I believe what you're looking for is the "Ensure Fields" macro, which is included in the Crew Macros pack, which you can download from here: http://www.chaosreignswithin.com/p/macros.html. It's a perfectly reliable set of macros created by Alteryx developers themselves.

After installation, simply add the Ensure fields macro prior to your cross-tab and it will "Ensure" the field(s) are present. You can simply select the Text option and write as many lines as you will have columns (each on it's own line).

Hope that helps.

Tom

[ScottE]

Hi @wzhu717 

Are you trying to get your "Total" formula to update based on how ever many fields, or in this case fruits, are added?

In the Crosstab aggregating options you can add a Total Column and you won't need a formula at all.

Hope this helps.

[wzhu717]

Hi Tom, 

thanks, how exactly do i use this tool? isnt it just creating a new column base on what name i put in? (same as the formula tool?) unless this tool checks if the excising columns in the workbook then if that column is not there then it creates a column?

[wzhu717]

hi, 

not really, i am just using that as an example, but the data i am using sometimes there are 4 fruits and sometimes there are 6 fruits. so i have add the new fruit into the formula tool each time when there is a new fruit. i am looking for a dynamic way to add the fruits together without typing in or deleting fruits from the formula tool. 

[ScottE]

@wzhu717 You should be able to accomplish that with just the Crosstab tool. 

[TomWelgemoed]

Hey @wzhu717 ,

Think @ScottE  has the right solution in mind for you. The one I suggested works better if you want to do a formula on a select few fields (that always need to be present), whereas if you just want to sum all data - @ScottE has the right & cleanest solution.

FYI, I couldn't open his file, but you can just tick "Total Column" in your CrossTab tool (as well as the Sum) and then you should neatly get your result without the formula tool.

Best,

Tom

[TomWelgemoed]

Hey, just to respond to this point: if you did ever need the Ensure fields macro, download the pack from the website given and run the installer after unzipping it. You will then close Alteryx, open it again and, like magic, you'll have a few more options in your menu to the top right :-).

I'd recommend downloading these anyway - there are some really handy tools, such as the Runner tools that allow you to chain workflows together and to read from multiple Excels (even if in different formats).

Take care,

Tom

[wzhu717]

yea about this...thanks for the suggestion but what if sometimes i have to exclude let say "apple", so the value can change over month to month, this month can be "apple' next month can be "pear" its all random. is there a way to sum them dynamically without manually add in the formula tool?  the point i am trying to get across its that if there are new fields appear i want to know if there is a  way to capture them in the formula tool so they are being included in the dynamically sum.  

[wzhu717]

thank you Tom. i have it downloaded but i try to understand how the tool is functioning?