Load and select

[Anonymous]

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

[sunny_talwar]

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

[sunny_talwar]

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;

[swuehl]

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.

[marcus_sommer]

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)];

next

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

[Anonymous]

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.

[

[Anonymous]

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

[sunny_talwar]

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

Best,

Sunny