Advent of Code is back! Unwrap daily challenges to sharpen your Alteryx skills and earn badges along the way! Learn more now.

Alteryx Designer Desktop Discussions

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

Macro to compare consecutive pairs of columns

jjc42
7 - Meteor

Hi,

 

I'm trying to figure out how to create a macro that will give the difference between column 3 and column 4, and then between columnn 5 and column 6, and so on.  It's easy enough to use the Multi-Field Formula tool to do this for one column pair, but I have about 180 columns and would rather not have 90 instances of the Multi-Field Formula tool in the workflow.   As an example, I'm starting with...

 

Date Order ID Column3 Column4 Column5 Column6 Column7 Column8

1/1/16

156498 158 785 444 265 164 945
1/2/16 187485 145 141 189 779 762 126
1/3/16 187698 589 274 189 865 159 753
1/4/16 485976 893 568 458 452 168 173

 

...and would like to end up with...

 

Date Order ID Column3 Column4 Column3_4Diff Column5 Column6 Column5_6Diff Column7 Column8 Column7_8Diff

1/1/16

156498 158 785 -627 444 265 179 164 945 -781
1/2/16 187485 145 141 4 189 779 -590 762 126 636
1/3/16 187698 589 274

315

189 865 -676 159 753 -594
1/4/16 485976 893 568 325 458 452 6 168 173 -5

 

It seems like a Macro would be the best way to do this, but I'm open to other solutions.  Thanks for the help.

3 REPLIES 3
MarqueeCrew
20 - Arcturus
20 - Arcturus

you can transpose the data with your date and field id. Then add a count to the fields as 0,1 (multi row). Then calc the diff from 1-0 multi rows. You'll have some tricks to crosstab the data back. But this is the way to solve the problem dynamically. 

 

Try with a few fields first. This approach will work for the 180 columns of data. 

 

Sorry, but it is Valentine's Day. Cant work tonight. 

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
s_pichaipillai
12 - Quasar

@jjc42

Mark pointed right direction .

if you still need some solution then try this one . it should work for Dynamic way :)

jjc42
7 - Meteor

@marqueecrew

Thanks for pointing me in the right direction.

 

@s_pichaipillai

Thanks for filling in that last part.  Did exactly what I needed it to.

 

Labels
Top Solution Authors