Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

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



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:



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,