Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
andydietler
Contributor

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
MVP
MVP

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

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.

3 Replies
MVP
MVP

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

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
Contributor

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

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

MVP
MVP

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

Community Browser