Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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