Mapping from multiple columns (conditional)

[tiverson]

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

[IraWatt · Accepted solution]

Hey @tiverson,

I think the attached workflow follows your logic:

The multi row fills in the Pos 3 so the formula always has something to reference to when applying your logic. 

If you have any questions or issues with the workflow make sure to ask :)

HTH,

Ira

[binuacs · Accepted solution]

@tiverson Another way of dong it

[kuoshihyang · Accepted 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! 

[tiverson]

@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

[IraWatt]

Hey @tiverson,

I've added the new data to my workflow and it still seems to work fine:

[kuoshihyang]

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.