Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
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

Re: Optimisation of date variable in script in QVW

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

Re: Optimisation of date variable in script in QVW

Will this speed it up?

MVP & Luminary
MVP & Luminary

Re: Optimisation of date variable in script in QVW

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

Re: Optimisation of date variable in script in QVW

what does autogenerate 1 do?

MVP & Luminary
MVP & Luminary

Re: Optimisation of date variable in script in QVW

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