Alteryx Designer Desktop Discussions

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

Applying multi row formula to multiple columns

aclar286
7 - Meteor

Hello, I was wondering if anyone could help me with this. 

 

I am using the Multi-row formula tool to apply the following formula to a column:

 

[RTL_N_24]-[Row-1:RTL_N_24]

 

To give the difference of values between rows.  I would like to apply the same formula to all of my columns, so only changing which current field is in the formula.

 

Is there any way I can do this easily?  I have over 80 columns to apply this to so it wouldn't be practical to add 80+ multi-row formula tools to my flow.  Is there another solution I am missing?

 

Thank you for your help!

17 REPLIES 17
JoshuaGostick
11 - Bolide

Hi @aclar286,

 

You may be able to do something like the workflow I've attached. What the workflow does is that it transposes the data so that all the records are in one column, then it applies the multi-row formula tool on that one column, and finally cross tabs the data back into its original format.

 

Multi-Row Formula on Multiple Columns.PNG

 

 

Let me know if you have any questions about the workflow or if it is not doing what you intend it to do.

 

Thanks,

Josh

ChrisTX
15 - Aurora

Try the Dynamic Replace Tool

It replaces data in multiple fields based on an expression or value.

aclar286
7 - Meteor

Thank you for your reply.  Unfortunately when I transpose the data the values are incorrect, I'm not sure why.  I have an alternative solution for the time being so will continue to look into it. 

Jonathan-Sherman
15 - Aurora
15 - Aurora

I would agree about using the multi-row after transposing and then cross tabbing back to get around your problem. Could we see how you've set it up?

jarrod
ACE Emeritus
ACE Emeritus

@patrick_digan built a multi-row, multi-field macro a while back. i can't find it, could you help out, Digan?

patrick_digan
17 - Castor
17 - Castor

It's still a work in progress, but perhaps it will help in your case! Wherever you save the yxzp, it will save a helpful word doc in one of the external folders. I'v attached the word doc and the full yxzp. Here is the link:

https://gallery.alteryx.com/#!app/Multi-Row-Multi-Field/5da9d45e0462d7179c6316cf

aclar286
7 - Meteor

Hi everyone, 

 

After a long week of checking and changing things I have realised that the first solution presented does work for me - it was in fact, the Excel spreadsheet I was trying to replicate in Alteryx that had cells copied across incorrectly.  This was therefore why I was getting a difference in values between the two!

 

Thank you so much everyone for your help and solutions, the Alteryx community has really helped me with this issue.

k_speik
5 - Atom

Hi @patrick_digan!  Is it possible you could upload this solution in in a file compatible with Designer 2019.1?  I'm unable to import the macro because it has been created in a newer version.

 

I tried running the version @Kenda posted in this thread, and while I'm able to open the macro in Designer it looks like I'm missing some additional pieces (see attached images.)

 

Macro in workflowMacro in workflow

 

Sub-macro Multi Field_iterative.yxmc tool creating errors in Multi Row - Multi Field.yxmc macroSub-macro Multi Field_iterative.yxmc tool creating errors in Multi Row - Multi Field.yxmc macro

 

Invalid tool in Multi Field_iterative.yxmc sub-macroInvalid tool in Multi Field_iterative.yxmc sub-macro

 

Error message when opening Multi Field_iterative.yxmcError message when opening Multi Field_iterative.yxmc

patrick_digan
17 - Castor
17 - Castor

@k_speik I changed the version to 2018.1 in the attached using 7-zip to open the yxzp as a zip and just edit the files manually.

Labels