community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
#SANTALYTICS

The highly anticipated Alteryx Community tradition is back! We hope you'll join us!

Learn More

Apply Column Rules to Multiple Columns in the Table Tool

Hello,

 

I have joined two files that each have about 60 columns. I want to be able to create a formula that subtracts file one from file two (think current minus prior year data). I know how to do this using the formula feature, but I don't want to create 60 formulas for each set of columns. Is there a way to create one formula that will subtract each column. I can place the columns next to each other using the select tool so ideally I would have column 2 minus column 1 and column 4 minus column 3 etc with each creating a new column to show the variance (i.e. subtraction result).

 

Thanks in advance!

Alteryx Partner

You could do this with a Transpose Tool -> Multi-Row Formula, and then CrossTab back into the original shape.

 

I've attached a screen shot below:

multicolumnrules.PNG

 

Essentially, I look for columns which contain "right" (not-case-sensitive), as these will be from file2. I then subtract the row above them, I then select only the differences, so I can coerce them into the format you're looking for, create an extra record ID with which to match my differences up with (to avoid a weird diagonal matrix looking dataframe), and join back in with the original data based on RecordID

 

I can go into more detail if you'd like, let me know if this helps,

 

Cheers!

Labels