How to use lag function in Alteryx?

Question

Hello everyone,

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

Account NumberAccount status DescAs of date1Active01-01-20192Close01-01-20183Charge off 01-01-20174Non-Accrual01-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 NumberAccount status DescAs of dateStatus1Active01-01-201902Close01-01-201813Close01-01-201524Charge off 01-01-201735Charge off01-01-201546Non-Accrual01-01-20165

Any help would be greatly appreciated!

Please let me know if you need more information!

Thank you!

Maskell_Rascal · Accepted Answer

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