Peek Function multiple rows

[anwarbham]

Hi has anyone used the Peek function to compare multiple rows.

i have the table below i am trying to generate the StatusFlag column with 1 for each row that has the week number greater than the MIN(WeekNum) in a series. where all the ID1, ID2, ID3 are the same .

it works if there are only 2 rows but if the number of rows is more than 2 in a group it does not work .

this is an example where i get the result i want 

ID1	Date1	WeekNum	ID2	ID3	ID4	StatusFlag
a3G0W0000045boaUAA	03-feb-20	6	005d0000002dP4qAAE	001d0000021DjpNAAS	a040W00001tZNdEQAW	0
a3G0W0000045boaUAA	06-mar-20	10	005d0000002dP4qAAE	001d0000021DjpNAAS	a040W00001u5VL6QAM	1

in this group because we have multiple rows in the group the result is incorrect week 26 should have StatusFlag 0 a the rest should be 1

ID1	Date1	WeekNum	ID2	ID3	ID4	StatusFlag
a3G0W000004KeYpUAK	2020-06-26	26	005d00000057VbrAAE	001d000001wKV8HAAW	a040W00001vCXzXQAW	1
a3G0W000004KeYpUAK	2020-07-01	27	005d00000057VbrAAE	001d000001wKV8HAAW	a040W00001vChDhQAK	1
a3G0W000004KeYpUAK	2020-07-13	29	005d00000057VbrAAE	001d000001wKV8HAAW	a040W00001vEef1QAC	1
a3G0W000004KeYpUAK	2020-07-22	30	005d00000057VbrAAE	001d000001wKV8HAAW	a040W00001vEZxBQAW	0
a3G0W000004KeYpUAK	2020-08-03	32	005d00000057VbrAAE	001d000001wKV8HAAW	a040W00001tHFaaQAG	0
a3G0W000004KeYpUAK	2020-08-07	32	005d00000057VbrAAE	001d000001wKV8HAAW	a040W00001tHoctQAC	0
a3G0W000004KeYpUAK	2020-08-10	33	005d00000057VbrAAE	001d000001wKV8HAAW	a040W00001tHDpyQAG	1
a3G0W000004KeYpUAK	2020-08-26	35	005d00000057VbrAAE	001d000001wKV8HAAW	a040W00001vpnb9QAA	1

$(vTable)_Tmp2:
LOAD
ID1,
Date1,
WeekNum ,
ID2,
ID3,
ID4,
if( WeekNum > peek(WeekNum, RowNo(), 'Adherance_Tmp1') , 1,0) as StatusFlag
Resident
$(vTable)_Tmp1
Order By
WeekNum 
;

[Ksrinivasan · Accepted solution]

hi,

try this

hhh:
LOAD
ID1,
Date1,
WeekNum,
ID2,
ID3,
ID4
FROM [lib:/SSSS1.xlsx]
(ooxml, embedded labels, table is ddiiss);
Join
Load ID1,
Max(Date1) as Date1,
1 as flag
Resident hhh
Group by ID1,;
NOConcatenate
TEST:
Load
ID1,
Date1,
WeekNum,
ID2,
ID3,
ID4,
if(flag = '1' , '1' ,'0') as statys
Resident hhh
Order by ID1,WeekNum;
drop Table hhh;

ksrinivasan.

[anwarbham]

Thanks @Ksrinivasan 

i managed use your code like this and got the result i needed

$(vTable)_Tmp2:
LOAD
ID1,
Date1,
WeekNum,
ID2,
ID3,
ID4
Resident
$(vTable)_Tmp1;

Join
Load 
ID1,
ID2,
ID3,
Min(WeekNum) as WeekNum,
1 as flag
Resident $(vTable)_Tmp2
Group by ID1,ID2,
ID3;

NOConcatenate
$(vTable)_Tmp3:
Load
ID1,
Date1,
WeekNum,
ID2,
ID3,
ID4,
if(flag = '1' , '0' ,'1') as statys
Resident $(vTable)_Tmp2
Order by ID1,WeekNum;
drop Table $(vTable)_Tmp2;