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
Solved! Go to Solution.
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
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.
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
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.