topic Re: Optimisation of date variable in script in QVW in QlikView

Optimisation of date variable in script in QVW

Wed, 27 Apr 2016 08:40:56 GMT

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?

Re: Optimisation of date variable in script in QVW

sunny_talwar — Thu, 12 May 2016 10:12:49 GMT

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

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

Missing Manual - Peek()

Re: Optimisation of date variable in script in QVW

Thu, 12 May 2016 10:14:03 GMT

Will this speed it up?

Re: Optimisation of date variable in script in QVW

marcus_sommer — Thu, 12 May 2016 10:21:42 GMT

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

Re: Optimisation of date variable in script in QVW

Thu, 12 May 2016 10:30:26 GMT

what does autogenerate 1 do?

Re: Optimisation of date variable in script in QVW

marcus_sommer — Thu, 12 May 2016 14:34:05 GMT

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