Regarding comparing the current row with the next row data

[#1]

Hi Friends,

Is there any way to compare the current row with the next row data in the script?

Like we can use peek() to compare with the previous row  but something for the current row with the next row.,

Thanks in advance!

Kiru

[pamaxeed]

Hi,

in Qlikview we have the previous() or peek() function.

If you are looking for a corresponding next() function, unfortunately it does not exist, but you can sort your data in the table like needed and then use the previous function. This gives the same result.

Cheers,

Patric

[maxgro]

reverse the sort order

[MK_QSL]

Your can use Previous/Peek Function with Resident Load and Order By your Fields in Descending Order

[#5]

Thanks so much for your reply guys and I sincerely appreciate your time.

I am not sure how sorting/reversing will handle my scenario.

I am using the below expression to compare the current product id with the previous row product id  and looking for date overlapping in their contracts periods .

If(productid= Peek(productid),If([Contract Start Date] < Peek([Contract End Date]), 1, 0),0) As Overlapping

Eg:

Productid, contractid,startdate,enddate

p1,c1,25/1/15,30/1/15

p1,c2,27/1/15,17/2/15

p1,c3,15/2/15,19/3/15

I am getting overlapping for contracts c2 and c3 but I also need it for contract c1 as well.

Any suggestions will be appreciated!

.

Thank you!

[MK_QSL]

Temp:
Load
  Productid,
  contractid,
  Date(Date#(startdate,'DD/M/YY')) as startdate,
  Date(Date#(enddate,'DD/M/YY')) as enddate
Inline
[
  Productid, contractid, startdate, enddate
  p1, c1, 25/1/15, 30/1/15
  p1, c2, 27/1/15, 17/2/15
  p1, c3, 15/2/15, 19/3/15
  p1, c4, 22/3/15, 25/5/15
];


Final:
Load
  Productid,
  contractid,
  startdate,
  enddate,
  if(Productid = Previous(Productid),
  If(startdate < Previous(enddate), 'Overlapping','OK')) as Flag
Resident Temp
Order By Productid, contractid;


Drop Table Temp;

If you want to display 'Overlapping' for c1 as well, use below expression for flag

if(Productid = Previous(Productid),

  If(startdate < Previous(enddate), 'Overlapping','OK'),'NewContract') as Flag

[maxgro · Accepted solution]

source:

load * inline [

Productid, contractid,startdate,enddate

p1,c1,25/1/15,30/1/15

p1,c3,15/2/15,19/3/15

p1,c2,27/1/15,17/2/15

p1,c4,27/1/16,17/2/16

p1,c4,27/3/16,17/4/16

p2,c1,25/1/15,30/1/15

p2,c3,15/2/15,19/3/15

p2,c2,27/1/15,17/2/15

p2,c4,27/1/16,17/2/16

p2,c4,27/3/16,17/4/16

p2,c4,27/5/16,17/7/16

p2,c4,27/6/16,17/8/16

];

final1:

NoConcatenate load

  *,

  if(Productid=Peek(Productid) and startdate<=Peek(enddate),1,0) as FlagOverlapping

Resident source

order by Productid, startdate;

DROP Table source;

final2:

NoConcatenate load

  Productid, contractid,startdate,enddate,

  rangemax(FlagOverlapping, if(Productid=Peek(Productid) and enddate>=Peek(startdate),1,0)) as FlagOverlapping

Resident final1

order by Productid, startdate desc;

DROP Table final1;

[#8]

Thanks so much and I sincerely appreciate your time Manish.

Is there any way we could make contract c1 also overlapping??

Because ,c2 get started before c1 ends for that product.

so I am looking for a solution like c1,c2,c3 as overlapping and c4 as ok.

Thanks again!

Kiru

[#9]

Thank you guys, I sincerely appreciate your time!

Finally got the one I wanted!

Kiru

[MK_QSL]

Temp:
Load
  Productid,
  contractid,
  Date(Date#(startdate,'DD/M/YY')) as startdate,
  Date(Date#(enddate,'DD/M/YY')) as enddate
Inline
[
  Productid, contractid, startdate, enddate
  p1, c1, 25/1/15, 30/1/15
  p1, c2, 27/1/15, 17/2/15
  p1, c3, 15/2/15, 19/3/15
  p1, c4, 22/3/15, 25/5/15
];


Temp2:
Load
  Productid,
  contractid,
  startdate,
  enddate,
  if(Productid = Previous(Productid),
  If(startdate < Previous(enddate), 'Overlapping','OK'),'NewContract') as TempFlag 
Resident Temp
Order By Productid, contractid;


Drop Table Temp;


NoConcatenate
Final:
Load
  *,
  If(TempFlag = 'NewContract' and enddate > Previous(startdate),'Overlapping',TempFlag) as FinalFlag
Resident Temp2
Order By Productid, contractid desc;


Drop Table Temp2;
Drop Field TempFlag;