Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Qlik Highlights 2020 Giveaway! Watch, reply and have a chance to win a $200 Amazon Gift Card! Watch Video
Showing results for 
Search instead for 
Did you mean: 

Upper and Lower Bound Data which gets superseded

Hello Qlik Gurus

Hopefully an easy one for the more experienced to answer.

I am mapping pricing over time.  The data I use has upper and lower bound dates (Start Date and End Date) and a ChangeDate (which is basically the date our system logs a new iteration of pricing).

Simple example:

Contract      Price       StartDate          EndDate            ChangeDate

ABC123           10          1/04/2020        31/05/2020       31/03/2020
ABC123           12         18/04/2020       31/05/2020      17/04/2020


This shows pricing being submitted to our system for 1 contract, beginning at $10 (line 1), but part way through the month the price changes upward to $12 (line 2).  The date range is adjusted accordingly for the 2nd line, with a new startDate.  The first line of pricing is not adjusted. 

So from 18/04/20 to 31/05/20, the initial price of $10 gets superseded, and should be ignored.

I am trying to get the data loaded into Qlik, so the end visualization looks something like this:


So far I have been successful generating one record per discrete date value using While and Iterno() functions in my load statement, thus:


FROM [lib://DataPark (mdl_walker)/Chris/Test.xlsx]
(ooxml, embedded labels, table is Sheet1);

   Date(StartDate + IterNo() - 1) as StartDate,

resident FIRST_TABLE

WHILE StartDate + IterNo() - 1 <= EndDate;

However (!), I'm not that familiar with this function, and I can't figure out how to leverage the ChangeDate value within the While and Iterno() functions to ignore generating data for those records that are being superseded, i.e.  in this case all the records from 18/04/20 to 31/05/20 with a price of $10 don't need to be generated because the price of $12 has superseded it.

Can anyone advise how I can achieve this please?

Thanks in advance


Labels (3)
3 Replies


this script will help you 

load * inline [
/////////////////adjusting end date for contracts
load *,
     date(StartDate+IterNo()-1) as dateField
     While  date(StartDate+IterNo()-1) <= ActualEndDate;
Load *,
     if(previous(Contract)=Contract,Previous(ChangeDate),EndDate) as ActualEndDate
Resident dataTemp
order by Contract, StartDate desc;

drop Table dataTemp;

Hi Lironbaram - oh wow this looks awesome.  I have just evaluated it from the code you sent, and it works perfectly.  I am just going to apply to my extended data set to check the outcome and if it continues to be perfect I will mark this as solved with your solution.  It is possible I may have a return question after the evaluation.  Thanks so much so far, and for being so quick.  This forum is superb.  Regards Giles


HI Giles 

happy to assist , feel free to ask any additional question