Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
I think you are missing single quotes around your table name in the Peek function
LET vSystemDateKeyMonthRolling25=Peek('SystemDateKeyMonthRolling25', 0, 'SystemDateKeyMonthRolling25');
Will this speed it up?
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
what does autogenerate 1 do?
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