Mapping from multiple columns (conditional)

Question

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

FS mapping (Alteryx).xlsx

IraWatt · Accepted Answer

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

binu_acs · Accepted Answer

@tiverson Another way of dong it

kuoshihyang · Accepted Answer

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!