Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
geogou1973
New Contributor II

Collecting data in data loading script with another way

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.

2 Replies

Re: Collecting data in data loading script with another way

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

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.