Discussion Board for collaboration on QlikView Scripting.
I have a script that creates record for each date between the start and end of a contract and links back to the contract fact table with %ContractKey. Which of these ways is going to load faster:
Use an Inner Join to drop the invalid rows:
//Get Min/Max Dates
Min([Contract Date Start]) AS MinDate,
Max([Contract Date End]) AS MaxDate
Let vMinDate = Peek('MinDate');
Let vMaxDate = Peek('MaxDate');
Drop Table MinMaxDate;
//Create Table of All Dates in Range
Date($(vMinDate) + IterNo() - 1) as [Active Date]
AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);
//Cartesian All Contracts to Dates
[Contract Date Start] AS Start_temp,
[Contract Date End] AS End_temp
//Remove Dates Outside of Contract Start and End
Inner Join (ContractDates)
WHERE Start_temp <= [Active Date]
AND End_temp >= [Active Date]
Drop Fields Start_temp, End_temp;
Or a similar method but with a temp table and then load a 2nd table and drop the temp one:
Rename Table ContractDates to ContractDates_temp;
Drop Table ContractDates_temp;
Or is there a better way to accomplish this?
Go to Solution.
I would give a WHILE clause a try:
Date([Contract Date Start]+ iterno()-1) AS ActiveDate
WHILE ([Contract Date Start]+ iterno()-1) <= [Contract Date End];
You can probably apply the WHILE clause already to your initial Contracts load.
Regarding your question about the performance, this might depend on your specific setting, so just run some tests.
Wow, that's slick. Thanks. I need to learn Iterno() better.
Here is something worth reading
Creating Reference Dates for Intervals
Generating Missing Data In QlikView