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:
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.
load * inline [
/////////////////adjusting end date for contracts
date(StartDate+IterNo()-1) as dateField
While date(StartDate+IterNo()-1) <= ActualEndDate;
if(previous(Contract)=Contract,Previous(ChangeDate),EndDate) as ActualEndDate
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