Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Applying multi row formula to multiple columns

6 - Meteoroid

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!

Highlighted
Alteryx Certified Partner

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

Highlighted
12 - Quasar

Try the Dynamic Replace Tool

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

Highlighted
6 - Meteoroid

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. 

Highlighted
Alteryx Certified Partner

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?

Highlighted
11 - Bolide
11 - Bolide

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

Highlighted
15 - Aurora
15 - Aurora

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

Highlighted
6 - Meteoroid

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.

Highlighted
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

Highlighted
15 - Aurora
15 - Aurora

@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