Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

how to add values with dynamic columns

wzhu717
8 - Asteroid

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? Capture.PNG

 

 

15 REPLIES 15
danilang
19 - Altair
19 - Altair

Hi @wzhu717 

 

You can do this all without using a formula at all by leveraging the Total column in the Cross Tab tool.  

 

c.png

 

The workflow looks like this

w.png

 

Both of the Crosstabs have the Sum and Total column options checked.  After the union, the Dynamic Rename removes the "Sum_" prefix and the Select moves the Total column to the end, resulting in 

 

r.png 

Since there's no formula tool, it will work for any input columns

 

Dan 

 

 

 

wzhu717
8 - Asteroid

Thanks, but what if i only need the sum for "Apple" + "Pear" +"Mellon"? and that can change every month, next month i would need to sum "Grape" + Peach"+"Pear"?

TomWelgemoed
12 - Quasar

Hi @wzhu717 ,

 

I really hope this provides answers to both your questions:

 

1. Using a dynamic calculation - there are multiple options. I add 2 that I can think of in the attached packaged workflow (the one mentioned yesterday was a 3rd option). 

  1. Use a join to only use the fields you're interested in. I've used your own data. Let's say this month the calculation should only include apples & bananas - put that in a 2nd input tool and the join will automatically only sum them. You can still join the non-matching values if you'd like as an extension of this example
  2. Use a dynamic formula (also part of Crew Macros). I've attached an example for you in the same workflow - it works on a similar principle in that you need to supply which fields needs to be considered. But it allows you to keep your "rule" separate and update it based on conditions (e.g. in February use this calculation, in March this one etc.)

2. On the Ensure fields question, I've added this in the workflow too. See the image below - it highlights how you could always "Ensure" that certain fields are present ... in the event that you need them for a calculation further down the workflow. Simply list them and choose the relevant data type (in this case Int).

 

Hope that does it! 🙂

 

Regards,

Tom

 

Ensure fields.png

 

 

danilang
19 - Altair
19 - Altair

Hi @wzhu717 

 

Here's a modification that allows you to specify which fruits you want to sum.  w2.png

 

The Fruits to sum contains a list of the Fruits that you want to total.  The Find Replace tool adds a new field to the input data if the fruit is in the list of fruits to sum.  The filter takes only only the records where this new field is not null and generates a total of only these fruits.  The total is appended to the cross tabbed(No total column) input giving you

 

r2.png

 

I used a Text Input for the list of fruits to sum, but you can replace this with an input data tool that reads an excel file or a List Box interface tool 

 

Dan

wzhu717
8 - Asteroid

this is what i am looking for. thank you .

wzhu717
8 - Asteroid

this is very helpful, thank you Tom.

Labels