Applying multi row formula to multiple columns

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!

Developer Tools

Accepted answers

JoshuaGostick

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

Multi-Row Formula on Multiple Columns.yxmd

All comments

JoshuaGostick

Hi @aclar286,

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

Multi-Row Formula on Multiple Columns.yxmd

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.

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

Multi -Row -Field -Column.docx

Multi-Row Multi-Field.yxzp

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.

jeanvaladez

Hi Josh,

I am trying to follow your example for a similar objective. However, I want to round all of my values in each column to a whole number (i.e. with out any decimals). I have been doing this by using the Formula Tool and using the following formula for each column:

"Round([ColumnName], 1)"

I would like to do this in a faster and much more efficient way as I now have over 100 columns and do not want to set up the Formula for each one of them.

Can I do this without having to type out the formula for each column using your example below?

Thank you,

Jeanette

JLMToth

Thank you so much! My team had a line of addresses and contact info pulling from PDFs but when a contact had more than one location they werent always submitting info for both - so we were able to sort by the contact and say if empty fill the one before it - complete list now! just used

IF ISNULL([ReportHeader])
THEN [Row-1:ReportHeader]
ELSE [ReportHeader]
ENDIF

Instead in the multi row. took us 2 days of failure before we found this--- again thank you!

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235