I am trying to create a column that list the values conditionally from multiple columns.
Data table sample
I am trying to create the new column using the condition that if Pos 1 is not empty, return that value. If it is empty, enter Pos 2. If that is empty, return Pos 3 (orange items).......The only change to this routine is that if there is an Account listed, I want Pos 3 listed as the position. (yellow items)
Desired output
Any help is appreciated, thanks
Solved! Go to Solution.
Hi, that is pretty easy to do in Alteryx. Just plop in a Multi-Row Formula tool and create a new field called Position (V_string).
Then for your formula just do the following:
If (isEmpty([Acct]) and isEmpty([Pos 2]) and isEmpty([Pos 3]) and !isEmpty([Pos 1])) Then [Pos 1]
elseIf (isEmpty([Acct]) and !isEmpty([Pos 2]) and isEmpty([Pos 3]) and isEmpty([Pos 1])) Then [Pos 2]
elseIf (!isEmpty([Acct]) and isEmpty([Pos 2]) and isEmpty([Pos 1]) and !isEmpty([Pos 3])) Then [Pos 3]
else [Row-1:Position]
endif
* I used a data cleanse tool to replace Nulls with blanks for my formula to check for isEmpty, you can also use isNull if you only have nulls.
Hope this helps!
@kuoshihyang @binuacs @IraWatt
Ugh, I actually had my accounts off by one row. I have attached a revised file.
The accounts do not start until the row after the Pos 3 amount
Just tweak the formula to check for the correct combination of isEmpty or isNull fields,
so for example: if (!isEmpty([Acct]) and isEmpty([Pos 1]) and isEmpty([Pos 2]) and isEmpty([Pos 3])) then [Row-1:Pos 3]... that should get you the value in the Pos 3 field for the previous record.