Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist III
Specialist III

help with interval match or may be scripting with dates

Hi Please see attached QVW file. in my table the key columns are combination of Itemkey and Key. And for each row of Item key & key i will have sales price which are active with in the date ranges (fromdate and ToDate). So my problem is whenever they change the price in the database the fromdate is changing but the todate remains the same. for example please see below: for the 3|1495.562.M the Sales price has been changed 3 times. But the Todate has changed only 2 times .

I need help in creating a column which gives me the correct date range when the Todate is not changed then assign correct Todate. The expected result is below highlighted in green.

Thanks for your help in advance....

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: help with interval match or may be scripting with dates

if I understand, add after your script

Newtest:

load

  *,

  if(ItemKey<>Peek(ItemKey) or Key<>Peek(Key), todate, date(peek(fromdate)-1)) as new_todate

Resident

  Test

order by ItemKey, Key, fromdate desc;

DROP Table Test;

1.png

View solution in original post

2 Replies
Highlighted
MVP
MVP

Re: help with interval match or may be scripting with dates

if I understand, add after your script

Newtest:

load

  *,

  if(ItemKey<>Peek(ItemKey) or Key<>Peek(Key), todate, date(peek(fromdate)-1)) as new_todate

Resident

  Test

order by ItemKey, Key, fromdate desc;

DROP Table Test;

1.png

View solution in original post

Highlighted
Specialist III
Specialist III

Re: help with interval match or may be scripting with dates

thanks a lot massimo ... you helped me a lot man... and i have posted another question in community. do you mind helping me on that http://community.qlik.com/message/695640#695640