This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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.
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
- 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.
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.