Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
MinMaxDate:
LOAD
Min([Contract Date Start]) AS MinDate,
Max([Contract Date End]) AS MaxDate
Resident Contracts;
Let vMinDate = Peek('MinDate');
Let vMaxDate = Peek('MaxDate');
Drop Table MinMaxDate;
//Create Table of All Dates in Range
ContractDates:
LOAD
Date($(vMinDate) + IterNo() - 1) as [Active Date]
AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);
//Cartesian All Contracts to Dates
Join (ContractDates)
LOAD Distinct
%ContractID,
[Contract Date Start] AS Start_temp,
[Contract Date End] AS End_temp
Resident Contracts;
//Remove Dates Outside of Contract Start and End
Inner Join (ContractDates)
LOAD
%ContractID,
[Active Date]
Resident 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:
//Get Min/Max Dates
MinMaxDate:
LOAD
Min([Contract Date Start]) AS MinDate,
Max([Contract Date End]) AS MaxDate
Resident Contracts;
Let vMinDate = Peek('MinDate');
Let vMaxDate = Peek('MaxDate');
Drop Table MinMaxDate;
//Create Table of All Dates in Range
ContractDates:
LOAD
Date($(vMinDate) + IterNo() - 1) as [Active Date]
AutoGenerate 1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);
//Cartesian All Contracts to Dates
Join (ContractDates)
LOAD Distinct
%ContractID,
[Contract Date Start] AS Start_temp,
[Contract Date End] AS End_temp
Resident Contracts;
//Remove Dates Outside of Contract Start and End
Rename Table ContractDates to ContractDates_temp;
NoConcatenate
ContactDates:
LOAD
%ContractID,
[Active Date]
Resident ContractDates_temp
WHERE Start_temp <= [Active Date]
AND End_temp >= [Active Date]
;
Drop Table ContractDates_temp;
Drop Fields Start_temp, End_temp;
Or is there a better way to accomplish this?
I would give a WHILE clause a try:
LOAD
%ContractID,
Date([Contract Date Start]+ iterno()-1) AS ActiveDate
Resident Contracts
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.
I would give a WHILE clause a try:
LOAD
%ContractID,
Date([Contract Date Start]+ iterno()-1) AS ActiveDate
Resident Contracts
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