How to get lead and lag of a column in Dataflow

Ankur Jain-47859

Summary

How to get lead and lag of a column in Dataflow

Content

Hi I have a data as below, where NK is natural key, I want to acheive something like

lag(job_code,1) over(partition by NK order by START_DT) prev_job_id

lead(job_code,1) over(partition by NK order by START_DT) prev_job_id

NK  START_DT    END_DT        JOB_CODE

100    01-JAN-10    30-JUN-10    J1    

100    01-JUL-10    31-MAR-11    J2

100    01-APR-11    31-AUG-12    J2

100    01-SEP-12    31-DEC-12    J3

100    01-JAN-13    15-NOV-13    J3

100    16-NOV-13    31-MAY-14    J2

100    01-JUN-14    31-DEC-14    J2

101    01-JAN-10    30-JUN-10    J1

101    01-JUL-10    31-MAR-11    J2

101    01-APR-11    31-AUG-12    J2

101    01-SEP-12    31-DEC-12    J3

101    01-JAN-13    15-NOV-13    J3

101    16-NOV-13    31-MAY-14    J2

101    01-JUN-14    31-DEC-14    J2

I tired to explore using 'Cumulative Value' step, but i guess it is meant for min max sum count on a window. Even in normal functions, there is rank but no lead lag. How to achieve lead , lag.