Applying multi row formula to multiple columns

[aclar286]

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!

[JoshuaGostick · Accepted solution]

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.

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]

Try the Dynamic Replace Tool

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

[aclar286]

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]

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]

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

[patrick_digan]

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]

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]

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 workflow

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

Invalid tool in Multi Field_iterative.yxmc sub-macro

Error message when opening Multi Field_iterative.yxmc

[patrick_digan]

@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.