topic Troubleshooting qvw script in QlikView

Troubleshooting qvw script

Thu, 15 Dec 2011 22:06:07 GMT

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

Troubleshooting qvw script

Thu, 15 Dec 2011 22:12:48 GMT

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;

Troubleshooting qvw script

Miguel_Angel_Baeyens — Thu, 15 Dec 2011 22:13:58 GMT

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

Troubleshooting qvw script

Miguel_Angel_Baeyens — Thu, 15 Dec 2011 22:16:15 GMT

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

Troubleshooting qvw script

Thu, 15 Dec 2011 22:21:03 GMT

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!

Troubleshooting qvw script

Miguel_Angel_Baeyens — Thu, 15 Dec 2011 22:25:08 GMT

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

Troubleshooting qvw script

Thu, 15 Dec 2011 22:26:02 GMT

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