Collecting data in data loading script with another way

geogou1973 — Thu, 21 Jan 2016 11:12:47 GMT

Hello i have the following script that i am collecting data. The number of the records are huge over 20.000.000 records per month in the level of item. With the following script i collect data from sales joining some information about stores and items and i store them into a QVD.

SALES:

LOAD ITEM_ID,

STORE_ID,

SALES_DATE,

SALES_WEEK,

SALES_YEAR,

SALES_MONTH,

SALES_QTY,

SALES_AMT,

SALES_AMT_FPA,

PRICE_LIST

FROM \\99.99.999.99\qvdstorage\SALES_2016_01.QVD (qvd);

LEFT JOIN

LOAD STORE_ID,

FORMAT,

STORE_DESCR,

STORE_REGION,

SQUARE_MEASURES,

FROM C:\Stores\stores.xls (biff, embedded labels, table is [Sheet 1$]);

LEFT JOIN

LOAD

ITEM_ID,

[SECTEUR DESCRIPTION],

[RAYON DESCRIPTION],

[DESCRIPTION FAMILY],

[DESCRIPTION SUBFAMILY],

[DECRIPTION UB],

[DESCRIPTION SUBUB]

FROM C:\QVDS\GLOBAL\ITEMS_GLOBAL.QVD (qvd);

STORE SALES INTO C:\QVDS\GLOBAL\SALES_GLOBAL_2016_01.QVD;

DROP TABLE SALES;

After that i'm doing the following in order to have the summaries in the level of Format, Store, Year,Date, e.t.c

LOAD SALES_YEAR,

SALES_MONTH,

SALES_DATE,

SALES_WEEK,

FORMAT,

STORE_ID,

(SUM(SALES_AMT_FPA)-SUM(SALES_QTY*PRICE_LIST))/SUM(SALES_AMT_FPA) AS CESSION_MARGIN,

SUM(SALES_QTY) AS QTY,

SUM(SALES_QTY*PRICE_LIST) AS COGS,

SUM(SALES_AMT) AS GROSS_SALES,

SUM(SALES_AMT_FPA) AS NET_SALES,

[SECTEUR DESCRIPTION],

[RAYON DESCRIPTION]

FROM C:\QVDS\GLOBAL\SALES_GLOBAL_2016_01.QVD (qvd)

WHERE FORMAT = 'HYPFL' OR FORMAT = 'HYPMI' OR FORMAT = 'SUPER' OR FORMAT = 'PROXI' OR FORMAT = 'CASHC'

GROUP BY SALES_YEAR,SALES_MONTH,SALES_WEEK,SALES_DATE,FORMAT,STORE_ID,[SECTEUR DESCRIPTION],[RAYON DESCRIPTION];

All this procedure it takes a lot of time. How can i rewrite it in order to decrease the time of reload ?

Thank you in advance.

Re: Collecting data in data loading script with another way

tamilarasu — Thu, 21 Jan 2016 11:58:52 GMT

Hi Gou Geo,

You can try apply map instead of Left join. I haven't tried but you can give a try. See

Mapping Load / Apply Map versus Left Join

Re: Collecting data in data loading script with another way

Anonymous — Thu, 21 Jan 2016 12:14:33 GMT

Your final LOAD is from a qvd and the WHERE and GROUP BY will make it a slow unoptimised load.

As this qvd was created and stored earlier in you script you could try loading it from resident instead and get rid of the STORE and move the DROP TABLE to the end.

topic Re: Collecting data in data loading script with another way in QlikView

Collecting data in data loading script with another way

Re: Collecting data in data loading script with another way

Re: Collecting data in data loading script with another way