Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Troubleshooting qvw script

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

1 Solution

Accepted Solutions
MVP
MVP

Troubleshooting qvw script

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

6 Replies
Not applicable

Troubleshooting qvw script

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;

MVP
MVP

Troubleshooting qvw script

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

MVP
MVP

Troubleshooting qvw script

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

Not applicable

Troubleshooting qvw script

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!

MVP
MVP

Troubleshooting qvw script

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

Not applicable

Troubleshooting qvw script

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

Community Browser