Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Multi Row Formula Tool

The_Voter
6 - Meteoroid

Hello! i have encountered an issue where i need to get direction separated into Outbound and Inbound by the following logic:
OUT_RET_1 is sending money (Outbound) 5 and IN_RET_2 is receiving (Inbound) money 5. How to apply multi row formula tool in this situation if it's applicable.Please find attached file with sample data and expected output. thank you in advance!


5 REPLIES 5
Emmanuel_G
13 - Pulsar

Hi @The_Voter ,

 

Do you want something like this ?

 

Emmanuel_G_1-1657632409126.png

 

mceleavey
17 - Castor
17 - Castor

Hi @The_Voter ,

 

this is not a task for the multi-row formula as that is specifically used to reference rows above and below the current line. You can learn more about that tool HERE.

This task can be completed by pivoting your data into rows using the delimiter in the DIRECTION column. The delimiter in this case is \sAND\s, that is a space followed by the word AND followed by another space. You can simply use this in the Text-to-Columns tool:

 

mceleavey_0-1657633098901.png

Ensure you have the "Skip empty columns" box checked.

This will split your data our into two rows, one for each of the directions.

You then simply apply the Sort tool to put it in the order you want:

 

mceleavey_1-1657633157962.png

 

mceleavey_2-1657633179678.png

 

 

I hope this helps,

 

M.



Bulien

FreeRangeDingo
11 - Bolide
11 - Bolide

@mceleavey What is the \s doing in your delimiter?  What does that specify?  Thanks!

binuacs
20 - Arcturus

@FreeRangeDingo \s is using as the delimiter for white space character. In the above example \sAND\s is equivalent to ' AND '

mceleavey
17 - Castor
17 - Castor

Hi @FreeRangeDingo ,

 

yeah, as @binuacs said it's the whitespace character, so in effect I'm using <space>AND<space> as the delimiter. This means I don't have to include another step to remove both the leading and trailing whitespace in the next step. Without it, you would get the following:

 

mceleavey_0-1657642932141.png

 

Regex is my friend.

 



Bulien

Labels