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: 
Not applicable

Optimisation of date variable in script in QVW

Hi,

My first question is there a way of finding out how optimised your load is, as in find out which parts are taking the longest?

Secondly, when I am reloading my qvw it looks like it gets stuck on loading this variable SystemDateKeyMonthRolling25.

This is the script I am using to generate this variable:

SystemDateKeyMonthRolling25:

LOAD DateKey AS [SystemDateKeyMonthRolling25]

FROM [..\QVD\DimDates.qvd] (qvd)

WHERE DateValue=date(AddMonths(Today(), -25));

LET vSystemDateKeyMonthRolling25=Peek('SystemDateKeyMonthRolling25', 0, SystemDateKeyMonthRolling25);

My question is there a more optimised way to do this?

5 Replies
sunny_talwar

I think you are missing single quotes around your table name in the Peek function

LET vSystemDateKeyMonthRolling25=Peek('SystemDateKeyMonthRolling25', 0, 'SystemDateKeyMonthRolling25');

Missing Manual - Peek()

Not applicable
Author

Will this speed it up?

marcus_sommer

Your qvd-load isn't optimized because you used a where-clause in the load-statement which isn't an exists(Field) statement which is the only where-clause exception for an optimized load.

But you could create one in this way:

temp:

Load date(AddMonths(Today(), -25)) as DateValue autogenerate 1;

SystemDateKeyMonthRolling25:

LOAD DateKey AS [SystemDateKeyMonthRolling25]

FROM [..\QVD\DimDates.qvd] (qvd)

WHERE exists(DateValue);

drop table temp;

For the variable-issue look at the suggestion from Sunny.

- Marcus

Not applicable
Author

what does autogenerate 1 do?

marcus_sommer

With autogenerate could be tables generic created - instead of the 1 could be also an expression which returned a numeric value and within the load could you with recno() react on the iteration - in short: it's a kind of a loop.

In this case it creates a single field with only one value and against this value runs then the where-exists-clause. More stuff for optimizations could you find here: Advanced topics for creating a qlik datamodel

- Marcus