topic Re: Load and select in QlikView

Load and select

Anonymous — Sun, 21 Jun 2015 12:54:34 GMT

Hi all ,

I am breaking the SalesDate into monthly data in loop (Assume salesdate consists of 12months data then i am seperating each month's data into seperate qvd and naming it as the same month name ).

My Sample code is

Table:

load distinct monthname(SalesDate) as Monthname;

sql select SalesDate from Db;

let vCount = noofrows('Table');

for i =0 to $(i)

let vmonth = peek('Table',$(i),'Monthname');

[$(vmonth)]:

load * where monthname(SalesDate)='$(vMonth)';

sql select f1,f2,f3,f4

where Year(Salesdate)>=(year(Current_Date)-1) and Salesdate<=Current_Date;

Store [$(vmonth)] into [$(vmonth)].qvd(qvd);

drop table [$(vmonth)];

next

now when i am executing the code it's taking more time to load for each month .Any help is appreciated

Thanks

Re: Load and select

sunny_talwar — Sun, 21 Jun 2015 13:09:49 GMT

Is the problem that it is taking lot of time to execute or is it giving you an error?

The one thing that stands out: You have used FOR i = 0 to $(i), I think you wanted to say FOR i = 1 to $(vCount)

Other than that it seems right, except there might be an issue with how the variables are used, to troubleshoot any issue there we would need to know are you getting any errors? If yes than where exactly are you getting it.

Best,

Sunny

Re: Load and select

sunny_talwar — Sun, 21 Jun 2015 13:13:13 GMT

One more thing, I would probably add the where statement to your first Table load so that only those month-year are loaded which you are late going to use in your qvd generation loop:

Table:

LOAD Distinct MonthName(SalesDate) as Monthname;

SQL Select SalesDate from Db

Where Year(Salesdate)>=(Year(Current_Date)-1) and Salesdate<=Current_Date;

Re: Load and select

swuehl — Sun, 21 Jun 2015 13:18:20 GMT

In addition, I think your second SQL SELECT should use a WHERE clause that limits the query to the YearMonth you are actually interested in, thus avoiding to retrieve data for months you filter in your preceding LOAD statement.

Re: Load and select

marcus_sommer — Sun, 21 Jun 2015 13:21:28 GMT

I think you will be speed up these loading if you made the sql-statement outside from loop and looped then over a resident-table maybe like this way:

tempSQL:

sql select f1,f2,f3,f4 from Db

where Year(Salesdate)>=(year(Current_Date)-1) and Salesdate<=Current_Date;

Table:

load distinct monthname(SalesDate) as Monthname Resident tempSQL;

let vCount = noofrows('Table');

for i =0 to $(i)

let vmonth = peek('Table',$(i),'Monthname');

[$(vmonth)]:

noconcatenate load * Resident tempSQL where monthname(SalesDate)='$(vMonth)';

Store [$(vmonth)] into [$(vmonth)].qvd(qvd);

drop table [$(vmonth)];

More speed could be possible if you stored the tempSQL as qvd and load from there especially if you added the Monthname or an equivalent within the SQL-SELECT and used this field to loop over the complete-data and as a filter-statement within a where exists clause.

- Marcus

Re: Load and select

Anonymous — Sun, 21 Jun 2015 13:24:43 GMT

Instead of the doing the distinct monthname(SalesDate) in QlikView you could back that off to the database and get the sql to only return to QlikView the distinct MonthYear. Your way you will be returning a row for every table row, which is inefficient at every level.

Similarly in the loop you in every pass repeatedly extract and drop all the sales data year to date. This will take longer and longer as the year progresses and you have more months to process You could either do the sql extract once before the loop and load from resident in the loop. Or generate sql that only returns data for the month in question from the database.

Your sql for the sales data extract does not have a from clause, I guess that was typo in your post.

[

Re: Load and select

Anonymous — Mon, 22 Jun 2015 05:27:12 GMT

Hi Sunny ,

It is for i= 0 to $(Count).Its my mistake.

Yes it is taking more time to load records .Example :for 1lakh records it is taking 4mins.

Thanks

Re: Load and select

sunny_talwar — Mon, 22 Jun 2015 10:04:11 GMT

May be you should follow what Marcus Sommer‌ and Bill Markham‌ has prescribed you. That may help you fasten up things.

Best,

Sunny