Lead or Lag Function

[brandt3076]

In Alteryx is it possible to create lead or lag functions where it can take the previous field from another row and add it as a field to its row as seen below.  I want to make an end date to my data for the user to understand which record was active during a specific point in time

Oracle Example:

LAG() OVER PARTITION(UNIQUE ID ORDER BY ...) 

Raw Data
Unique Id Start Date
234          20140101
456          20151001
456          20161201
789          20151001
789          20161101


Derived data
Unique Id  Start Date End Date
234           20140101 99991231
456           20151001 20161130
456           20161201 99991231
789           20151001 20161031
789           20161101 99991231

[jdunkerley79]

You can use a multi-row formula tool and set it to group by Unique Id.  I would also set it to return NULL if the record does not exist.

An expression like:

IIF(IsNull([Row+1:Start Date]),'99991231',[Row+1:Start Date])

will create the example you showed.

If you want to pick up from previous instead of next, switch the +1 to a -1.

Sample attached

[MarqueeCrew · Accepted solution]

Hello again @brandt3076!

Using a multi-row formula, you can look ahead and perform the calculations.  Here is a picture where the end dates are calculated (leaving NULL for default value).  A workflow is also attached.

 

Cheers,

Mark

[jdunkerley79]

Missed the plus/minus 1 correction

[brandt3076]

Thank you both as this has worked perfectly!

[adhe1811]

Hi ,

I am trying to replicate the functionality on my dataset that contains the following data :

KEY_NO,MO_YR_KEY_NO,BONUS_LEVEL.

The data has multiple records for every KEY_NO,MO_YR_KEY_NO combinations. I want to get the BONUS_LEVEL for the next MO_YR_KEY_NO but not the next row.I am attaching a sample data set(sample csv) and my workflow with the required values.It would be great if someone can help me this.

[marine]

Hi

I would simply create a lag from an existing variable

I know in SAS is simply lag(X) but I got that in Alteryx we have to use a multirow formula

I really don't understand how it works

Thank you for your answer