Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
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()

Highlighted
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