Multi Row / Multi Field formula: field+-

[PiotrJarzyna]

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

[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?

- 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

[alex · Accepted solution]

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.

[gc]

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.

[PiotrJarzyna]

Thank you All for the answers. 

Solution proposed by Joe_Mako is great, however I started to read this topic from the last post up and this is why I used the Transpose and CrossTab. 

The only thing that bothers me is why there is no Column+- functionality which could be used in multirow/multicolumn tools. Row+- exists and works great. I we could combine Column/Row+- we could limit this task to a single tool.

Kind regards,

Piotr