How to use lag function in Alteryx?

[tk12]

Hello everyone,

I'm working on data set that looks like below:

Account Number	Account status Desc	As of date
1	Active	01-01-2019
2	Close	01-01-2018
3	Charge off	01-01-2017
4	Non-Accrual	01-01-2016

What I want is, if previous account status is active and  if status is in (Close, Charge off, Non-Accrual) after that  add new column status with value 1.

Here is SAS code I have .

if lag(accountstatusdescription) = 'Active' AND accountstatusdescription in ('Chargeoff', 'Closed', 'Non-Accrual') then status = 1; 

if status > 0 then status + 1;

if lag(accountstatusdescription) = 'Active' AND accountstatusdescription in ('Chargeoff', 'Closed', 'Non-Accrual') then status = status - 1;

Account Number	Account status Desc	As of date	Status
1	Active	01-01-2019	0
2	Close	01-01-2018	1
3	Close	01-01-2015	2
4	Charge off	01-01-2017	3
5	Charge off	01-01-2015	4
6	Non-Accrual	01-01-2016	5

Any help would be greatly appreciated! 

Please let me know if you need more information! 

Thank you! 

[Maskell_Rascal · Accepted solution]

Hi @tk12 

You can get the desired results using the Multi-Row Formula tool. 

IF [Account status Desc]='Active' 
THEN 0 
ELSEIF [Account status Desc]!='Active' AND [Account status Desc]!=[Row-1:Account status Desc] 
THEN 1 
ELSE [Row-1:Status Alteryx]+1 
ENDIF

This is a great tool that will allow you to reference rows forwards/backwards, and you can even group by different fields like an account number. 

Interactive lesson on Multi-Row Formula https://community.alteryx.com/t5/Interactive-Lessons/Multi-Row-Formula/ta-p/82872 

If this solves your issue please mark the answer as correct, if not let me know!

Thanks!

Phil

[tk12]

Hello Phil,

Thank you very much. This is was very helpful. Will mark as complete. Thanks!