Multi Row / Multi Field formula: field+-

Hi,

I'm new with Alteryx but I found most of the information I need on this portal. However there is one thing which is causing me a lot of trouble and I could not find straight forward answer to my question.

Is there a way of applying formula to a column +-1 the same way I can use Row+- in Multi-Row Formula tool? If so, which tool should I use, Multi-Row or Multi-Field?

Example expression below - in bold you can see what I would like to achieve (obviously this expression does not work).

IF [_CurrentField_] = 10 or [_CurrentField_] = 100 or [_CurrentField_] = 1000 or [_CurrentField_] = 10000 then [Column+1:_CurrentField_] / [_CurrentField_] else [_CurrentField_] endif

What I'm dealing with is a set of data which contains a number of columns which are sorted in a way that in one column is used as an indicator (multiplier) which contains 1 or 100 or 1000 or 10000 and in the next column is always a value (for example 5.6789). What I would like to do is to apply a rule which will divide value by multiplier if multiplier is 100 or 1000 or 10000. I can only determine which column is a multiplier and which contains value by checking if it's content equals 1 or 100 or 1000 or 10000 (this will be consistent through out all of the rows). I would like to avoid creating static column names or id - data may vary between months.

If you need more information, please let me know.

Kind regards,

Piotr

Transformation

Preparation

Expression

Accepted answers

alex

I mocked up a solution, but without knowing how your data is set up you would need to modify it to suit your needs. Let me know if this a step in the right direction.

val to multiply.yxmd

All comments

JohnJPS

Hi Piotr,
One thing you could try is to fully transpose rows/columns to columns/rows, in which case MultiRow Formula is acting on what used to be columns. When finished, fully transpose back.
Hope that helps!
John

Joe_Mako

How about the attached?

dynamic replace.png

- Field Info to get a list of all the fields and their data types
- Multi-Row Formula to make "_NextField_" with the formula:

IF [Type] IN ("Byte", "Int16", "Int32", "Int64", "FixedDecimal", "Float", "Double")
AND [Row+1:Type] IN ("Byte", "Int16", "Int32", "Int64", "FixedDecimal", "Float", "Double")
THEN [Row+1:Name] ELSE Null() ENDIF

This is checking the data type of the current field and next field are both numeric, and then getting the name of the next field
- Filter to only keep fields that have a _NextField_
- Formula tool to create a two fields, "Boolean Expression" is just the string value of "1" because that will evaluate to a value of True (we want the Output Value to be computed for all these fields), and Output Value is basically a copy paste of your formula with the _NextField_ value concatenated in:

"IF [_CurrentField_] = 10 or [_CurrentField_] = 100 or [_CurrentField_] = 1000 or [_CurrentField_] = 10000 then ["+[_NextField_]+"] / [_CurrentField_] else [_CurrentField_] endif"

- Dynamic Replace with the option "Values are Expression/Formulas" checked

dynamic replace.yxmd

alex

I mocked up a solution, but without knowing how your data is set up you would need to modify it to suit your needs. Let me know if this a step in the right direction.

val to multiply.yxmd

That's what I was going to recommend. Transpose to get the columns you want for calculation into a Name, Value pair. Do your Multirow formula or other change on the Name, Value pair. Then CrossTab back into the original table format. Might need to add a RecordID to all original rows before you Transpose in order to CrossTab back. This pattern will be one you likely use over and over again. Worth a try.

PARESH

Hello,

I have multiple fields (Which has weekdays) where I am checking if days = "Saturday" or "Sunday" then I need to segregate those row items, any support highly appreciated.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703