Troubleshooting qvw script

[#1]

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

[#2]

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;

[Miguel_Angel_Baeyens]

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

[Miguel_Angel_Baeyens · Accepted solution]

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

[#5]

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!

[Miguel_Angel_Baeyens]

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

[#7]

Great thanks for info. I am running qv 10 SR3, by the way.