Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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:

chart.jpg

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

Labels (3)
3 Replies
Highlighted
Partner
Partner

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;
Highlighted
Creator
Creator

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

Highlighted
Partner
Partner

HI Giles 

happy to assist , feel free to ask any additional question