Upper and Lower Bound Data which gets superseded

[gileswalker]

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:

FIRST_TABLE:
LOAD
   Contract,
   Price,
   StartDate,
   EndDate,
   ChangeDate

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

SECOND_TABLE:
   Load
   Contract,
   Price,
   Date(StartDate + IterNo() - 1) as StartDate,
   ChangeDate

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

Giles

[lironbaram]

Hi 

this script will help you 

dataTemp:
load * inline [
Contract,Price,StartDate,EndDate,ChangeDate
ABC123,10,01/04/2020,31/05/2020,31/03/2020
ABC123,12,18/04/2020,31/05/2020,17/04/2020
ABC124,8,10/04/2020,30/04/2020,09/04/2020
ABC124,15,20/04/2020,30/04/2020,19/04/2020
];
/////////////////adjusting end date for contracts
Data:
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;

[gileswalker]

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

[lironbaram]

HI Giles 

happy to assist , feel free to ask any additional question