topic Re: Incremental Load for Multiple Excels using filename as PK in QlikView

Incremental Load for Multiple Excels using filename as PK

vikasmahajan — Tue, 09 Feb 2016 08:14:56 GMT

Dear Team,

I have excel files like Jan,Feb,Mar ... etc Sample 2 file are attached.

Requirement :

1. Creation of qvd store all data into Single QVD (ALLDATA.QVD)

2. if user changed data into excel files again same name file will be uploaded into directory , And modified excel file

should get updated into ALLDATA.QVD and old records should get deleted.

Please note : - I require script using my excels files only , so can any one help me to achieve the same.

Vikas

Re: Incremental Load for Multiple Excels using filename as PK

MindaugasBacius — Tue, 09 Feb 2016 08:29:22 GMT

For the first requirement:

LET path = '\\your path';

SUB DoDir(Root)
FOR each File in filelist( Root & '\*.xlsx') 
  Order:
  LOAD *
  FROM
  [$(File)]
  (biff, no labels, table is [Sheet1$]);
NEXT File  
  
FOR each Dir in Dirlist (Root&'\*')  
     CALL DoDir(Dir)  
NEXT Dir  
  
END SUB   
  
CALL DoDir('$(path)\Folder') 

STORE Order into order.qvd (qvd);

This script allows to load all xlsx files from one folder into single table. At the end it stores the data.

Re: Incremental Load for Multiple Excels using filename as PK

kaushiknsolanki — Tue, 09 Feb 2016 09:02:57 GMT

Hi Vikas,

Have a look at the link below.

https://community.qlik.com/thread/61585

Regards,

Kaushik Solanki

Re: Incremental Load for Multiple Excels using filename as PK

vikasmahajan — Tue, 09 Feb 2016 09:22:38 GMT

Hi Kaushik,

Thanks for reply actually I want to store into QVD and every time wants to update qvd with modified file if user copy modified excels into folder.

Vikas

Re: Incremental Load for Multiple Excels using filename as PK

Peter_Cammaert — Tue, 09 Feb 2016 09:50:23 GMT

Step-by-step approach:

Figure out the creation date of ALLDATA.QVD or (on very first load when ALLDATA.QVD doesn't exist yet) set date to something early (like Jan 1st, 1980)
Create new table NewAllData and load it with all Excels whose file modification date is more recent than date from step 1
From ALLDATA.QVD, load all months that do not exist yet in NewAllData.
Store NewAllData into ALLDATA.QVD

Best,

Peter

Re: Incremental Load for Multiple Excels using filename as PK

vikasmahajan — Tue, 09 Feb 2016 10:01:38 GMT

Thanks peter

can you provide script for the same ?

vikas