Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
So it takes 8 minutes for my script to year two variables. There 34 million rows in my qvd file. Should it take that long to create the variables? Is there a better way to do this?
temP:
Load
num(min(TransactionDate)) AS MinDate,
num(max(TransactionDate)) as MaxDate
Resident Transactions
Thanks, Alex
Hi alex,
That part shoudn't take long, but again, it will depend on the number of rows it has to autogenerate. Think of a null value in the vMinDate variable, or a very small value like 1910 instead of 2010...
Regards.
Miguel
Here is the rest of my temp table script
LET vMinDate = peek('MinDate', 0, 'TEMP');
LET vMaxDate = peek('MaxDate', 0, 'TEMP');
DROP TABLE TEMP;
DateIsland:
LOAD
date($(vMinDate) + rowno() - 1, '$(DateFormat)') AS D,
year($(vMinDate) + rowno() - 1) AS Y,
month($(vMinDate) + rowno() - 1) AS M,
If(date($(vMinDate) + rowno() - 1) = $(vMaxDate), 1, 0) AS YTDFlag1,
If(date($(vMinDate) + rowno() - 1) = AddYears($(vMaxDate), -1), 1, 0) AS LYFlag1,
date(monthstart($(vMinDate) + rowno() - 1), 'MMM-YYYY') AS MY
//InYearToDate(Date(IterNo() + Date($(vMinDate))), Date($(vMaxDate)), -1) AS LYFlag,
AUTOGENERATE vMaxDate - vMinDate + 1;
Hi Alex,
Although load time will vary largely depending on your hardware, running background services and so, you are loading from a resident table, and that usually takes some time. Did you try loading from a QVD instead if possible?
Hope that helps.
Miguel
Hi alex,
That part shoudn't take long, but again, it will depend on the number of rows it has to autogenerate. Think of a null value in the vMinDate variable, or a very small value like 1910 instead of 2010...
Regards.
Miguel
Completely forgot that loading resident table takes longer. As soon I changed it the qvd file it reduced the load time by 9 minutes. It still took a minute to generate the temp table. But that is a significant amount of time saved. Thanks Miguel!
Hi Alex,
Glad to help. Starting with version 10 QlikView increases performance loading resident tables and rendering charts because it takes advantage of the hardware multi threading technologies, so what was done by one core now it's done by several. But anyway a resident table is still in memory, and usually reading from QVD is a faster option.
Regards.
Miguel
Great thanks for info. I am running qv 10 SR3, by the way.