Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
andydietler
Partner - Creator
Partner - Creator

Which is faster to drop records, inner join or non-concatenate load and drop?

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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

3 Replies
swuehl
MVP
MVP

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.

andydietler
Partner - Creator
Partner - Creator
Author

Wow, that's slick. Thanks. I need to learn Iterno() better.