I am trying to create a price list based on date ranges, but couldn’t find any solution for my problem.
This is my table with lot of records based on Material number and ValidFrom to ValidTo.
MatN
Price
ValidFrom
ValidTo
100052
5.1
2021-01-01
2021-12-31
100052
2.36
2016-01-01
2020-12-31
100879
69.1
2015-01-01
2015-04-12
The solution should be show the PreviousYear, CurrenYear and Next Year based on Months.
MatN
Price
Date
100052
2.36
2020-01
100052
2.36
2020-02
100052
2.36
2020-03
….
100052
5.1
2021-01
100052
5.1
2021-02
100052
5.1
2021-03
….
100879
69.1
2020-01
100879
69.1
2020-02
100879
69.1
2020-03
…
If the record (MatN and Price) don’t exist for the PreviousYear, CurrentYear or NextYear, than take the last price (don’t matter if ist the price of 2015).
My code works, but shows not the PreviousYear, CurrentYear and NextYear. It shows only the dates between the date ranges ValidFrom to ValidTo, but I need PY, CY and NY.
To handle this in Qlik you would use the Concept of a Master Calendar and then a function called INTERVAL MATCH.
A Master Calendar would be a table that has a row for every single {entity}. Day. Hour. Minute. Second. Finest grain you care about.
The Interval Match function will end up creating a cross reference like you desire so every single day would have an entry for the price for that day.
I have attached a zip file that contains everything I used for a webinar years ago. It contains lots of samples inside the same QVF file. One is for a simple Grades problem. Classic 90-100 is A, and students scores. Another example is labor data where I need to know how many employees were working every minute of the day.
