Early bird tickets for Inspire 2023 are now available! Discounted pricing closes on January 31st. Save your spot!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.
SOLVED

Multi Row / Multi Field formula: field+-

PiotrJarzyna
6 - Meteoroid

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

5 REPLIES 5
JohnJPS
15 - Aurora
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
12 - Quasar

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

alex
11 - Bolide

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.

valtomult2.JPGvaltomult3.JPGvaltomult1.JPG

gc
9 - Comet

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
6 - Meteoroid

Thank you All for the answers. 

 

 

 

Labels