Using Peek in For loops- Outer and inner for loop

[shraddha6369]

Hello Everyone,

We have a requirement where we have 'Leaves' data. That has person ID, Account ID, Roster ID, Leave reason, Start date (for leave) , stop date for leave. 

And we need to find rows where for a same person id, same account , same roster -  the difference between start date and previous row stop date is 1 (that means the leaves are consecutive) then we need  new field with last row's star date and current rows stop date and the leave reasons must be concatenated/added  (like Leave reason previous row/Leave reason current row) in the CURRENT ROW.

So basically the time when that person was on continuous  leave we need to merge those records . This check should go down the for loop for each row compared with all previous rows where Person ID, Account ID and roster is same.

I am trying to achieve this  using peek and 2 for loops, one for id current and previous rows date comparison and then loop from that previous row all the way to the row where the diff in dates is not 1.

Please attached excel for sample data value and expected output.

Any help is much much appreciated. Difficulty is how to reference the variable (i) from the for loop to the current row in the table. 

[Saravanan_Desingh · Accepted solution]

Try this,

tab1:
LOAD RowNo() As RowID,* INLINE [
    PersonID, AccoutnID, RosterID, Leave reason, Start date, Stop date
    6411, 100, 200, Short term, 5/1/2018, 6/7/2018
    6411, 100, 200, Long term, 8/1/2018, 6/9/2019
    6411, 100, 200, Extended Long term, 6/10/2019, 7/9/2019
    6411, 100, 200, Bonus Loong term, 7/10/2019, 8/9/2019
    6411, 100, 200, Health, 8/10/2019, 
    5321, 300, 200, Family, 1/1/2021, 6/1/2021
    5321, 300, 200, Personal, 6/2/2021, 6/10/2021
    5321, 300, 200, Holiday, 6/11/2021, 7/1/2021
];

tab2:
NoConcatenate
LOAD *, If(Len(Trim([Stop date]))=0,'12/31/2099',[Stop date]) As Stop_Date,
 	If(Peek(PersonID)=PersonID And Peek(AccoutnID)=AccoutnID And Peek(RosterID)=RosterID And Peek([Start date])-[Stop date]=1,Peek(K1), RangeSum(Peek(K1),1)) As K1;
LOAD *
Resident tab1
Order By PersonID, AccoutnID, RosterID, [Start date] Desc;

Left Join(tab2)
LOAD PersonID, AccoutnID, RosterID, K1,
	Date(Min([Start date])) As [New Start date],
	Date(If(Max([Stop_Date])<>'12/31/2099',Max([Stop_Date]))) As [New Stop date],
	Concat([Leave reason],'/') As [New Leave reson]
Resident tab2
Group By PersonID, AccoutnID, RosterID, K1;

Drop Table tab1;
Drop Field K1, Stop_Date;

[shraddha6369]

Hi,

This exactly the works as per the use case. I really appreciate your time and help for this. Thanks much!

Will mark this as a solution. Thanks again!

Shraddha