We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.

Alteryx Designer Desktop Discussions

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

Alteryx Multi Row Formula Data Transformation

altrx_guy
5 - Atom

Hi Everyone,

 

Here is what I am trying to do. The first table you will see is input table whereas what I want to see is the output table

 

Input Table -   

LetterValue
A80
A80
A80
A80
A63
A63
A63
A80
A80

 

 Output Table

 

LetterValueStatus
A80Before
A80Before
A80Before
A80Before
A63During
A63During
A63During
A80After
A80After

I want to get the output table (Creating a new column 'Status') from the input table. This is just a sample data. If there is 80 all along, the status should be 'Before'. But if there is a change (as you can see 63 from 80, the status should be 'During' and it should be 'During' as long as it is 63. And when it changes again to 80, the status should be 'After'. There can be many values but the logic should be the same. Please help in this scenario! I think multi-row formula is the best tool to achieve this. You can use as many data points as you want but the goal is clear. Thank you so much!

1 REPLY 1
Felipe_Ribeir0
16 - Nebula

Hi @altrx_guy 

 

Try this formula inside of your multi-row tool:

 

 

IF [Value] != [Row-1:Value] and [Row-1:Value] = Null() 
THEN 'Before' 
ELSEIF [Value] != [Row-1:Value] and [Row-1:Status] = 'Before'
THEN 'During' 
ELSEIF [Value] != [Row-1:Value] and [Row-1:Status] = 'During'
THEN 'After'
ELSEIF [Value] != [Row-1:Value] and [Row-1:Status] = 'After'
THEN 'Before'
ELSE [Row-1:Status]
ENDIF

 

 

Screenshot 2023-08-29 163831.png

Labels