Lead or Lag Function

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

Developer Tools

Accepted answers

MarqueeCrew

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.

Screen Shot 2017-02-02 at 11.51.23 AM.png

Cheers,

Mark

Lead or Lag.yxmd

All comments

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

Lag Sample.yxmd

MarqueeCrew

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.

Screen Shot 2017-02-02 at 11.51.23 AM.png

Cheers,

Mark

Lead or Lag.yxmd

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.

alteryx_query_leadfunction.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703