community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Lead or Lag Function

Meteoroid

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

 

 

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

Alteryx Certified Partner
Alteryx Certified Partner

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.

Missed the plus/minus 1 correction

Meteoroid

Thank you both as this has worked perfectly!

Atom

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.

 

 

Atom

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

Labels