topic Re: Regarding comparing the current row with the next row data in QlikView

Regarding comparing the current row with the next row data

Mon, 21 Sep 2015 17:58:23 GMT

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

Re: Regarding comparing the current row with the next row data

pamaxeed — Mon, 21 Sep 2015 18:11:21 GMT

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

Re: Regarding comparing the current row with the next row data

maxgro — Mon, 21 Sep 2015 18:13:19 GMT

reverse the sort order

Re: Regarding comparing the current row with the next row data

MK_QSL — Mon, 21 Sep 2015 18:32:08 GMT

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

Re: Regarding comparing the current row with the next row data

Mon, 21 Sep 2015 18:32:29 GMT

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!

Re: Regarding comparing the current row with the next row data

MK_QSL — Mon, 21 Sep 2015 18:44:23 GMT

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

Re: Regarding comparing the current row with the next row data

maxgro — Mon, 21 Sep 2015 18:48:19 GMT

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;

Re: Regarding comparing the current row with the next row data

Mon, 21 Sep 2015 18:50:00 GMT

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

Re: Regarding comparing the current row with the next row data

Mon, 21 Sep 2015 18:57:52 GMT

Thank you guys, I sincerely appreciate your time!

Finally got the one I wanted!

Kiru

Re: Regarding comparing the current row with the next row data

MK_QSL — Mon, 21 Sep 2015 18:57:55 GMT

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;