Want to Generate Dates Between a Range

[devlinlee]

Good afternoon! I'm currently working on a Qlik app for our PO history and I've run into a problem that I can't figure out on my own, so I'm hoping someone smarter than I can give me a hand!

For the app, we have a field we'll call LAST_UPDATED which is the last date the price for an item was updated. I need to generate all dates in this entire range so I can join it with our master table and compare the updated price at the time to the price paid at the time (if that makes sense).

Ex. Let's say Item A was updated on 7/13/2020 with a price of $1.00 and wasn't updated again until 12/31/2020. We bought the item at a price of $1.05 on 8/20/2020. I want to be able to generate every date between 7/13 and 12/31 and have the price be $1.00 so when I join, it will compare the right price at the time of purchase, etc.

Thanks for any and all help y'all can provide! 

[rwunderlich]

Take a look at this downloadable example:

Qlikview Cookbook: Expand A Pricing Date Table https://qlikviewcookbook.com/recipes/download-info/expand-a-pricing-date-table/

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[oskartoivonen]

I made an example for you that should do exactly what you need. Key is to sort the price table, use a inter-record function such as Peek to establish the active date range for each price. The current active price should be given a future date like a dynamic +10 years (hardcoded 2030 in my example). With the date range established for each price you can intervalmatch with the fact table to generate each relevant date key that you need. You could also just generate a table with every single date between the minimum startdate and maximum enddate and intervalmatch that, but it'd generate way more rows than necessary.

Script:

Fact:
LOAD * INLINE [
	ProductID, %Key.Calendar, #Quantity
	A, 5.1.2022, 5
	A, 10.1.2022, 5
	A, 20.1.2022, 5
	A, 25.2.2022, 10
];


Price_Temp:
LOAD * INLINE [
	ProductID, LAST_UPDATED, #Price
	A, 1.1.2022, 100
	A, 1.2.2022, 110
];

Price:
LOAD
	*,
	Date(Coalesce(Peek(LAST_UPDATED), MakeDate(2030, 1, 1))) as MaxDate
RESIDENT
	Price_Temp
ORDER BY
	LAST_UPDATED desc;

drop table Price_Temp;

// exit script;

PriceIntervalMatch:
INTERVALMATCH(%Key.Calendar, ProductID)
LOAD DISTINCT
	LAST_UPDATED,
	MaxDate,
	ProductID
RESIDENT
	Price;

LEFT JOIN (Price) // Key: ProductID + LAST_UPDATED + MaxDate
LOAD * RESIDENT PriceIntervalMatch;

drop table PriceIntervalMatch;


LEFT JOIN (Fact) // Key: ProductID + %Key.Calendar
LOAD
	ProductID,
	%Key.Calendar,
	#Price as #Price.Fact
RESIDENT
	Price;

drop field %Key.Calendar from Price;

Data:

Data model: