Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
brunobertels
Valued Contributor II

incremental load , multiple excel file in the same folder

Hi All, 

I have a folder with daily excel files added for every opened days in that format ddmmyy.xlsx 

each files as the same structure but need all to be transform and clean to reduced useless rows 

each files has 1200 rows at the beginning and may be 100 after transform 

 

So to avoid reloading every day All the data and transform every day all the file i would like to use an incremental load with a store qvd command 

 

but i don't know how to modify my actual script to achieve that 

 

Here below my actual script , if some one could help me it will be great 

 

Thanks in advance 

 

[Sheet1$]:
LOAD
[@1],
[@2],
[@3],
[@6],
[@7]
FROM [lib://DATA_QUOT_HERMES/*.xls]
(biff, no labels, table is Sheet1$);

Temp:
LOAD

//Récupère la DATE:
if(wildmatch(left([@1],2),'Le'),
date(date#(right([@1],10),'DD/MM/YYYY')),peek(DATE_JOUR)) as DATE_JOUR,

// rècupère la CAMPAGNE
if(wildmatch([@1],'*Campagne*'),[@1],peek(CAMPAGNE)) as CAMPAGNE,

// rècupère le TYPE Appels ou Rappels et Tri 'APPELS +RAPPELS' Appels + Rappels
if(wildmatch([@1],'Appels','Rappels'),[@1],
if(wildmatch([@1],'Appels + Rappels'),[@1],
peek(TYPE))) as TYPE,

// rècupère l'intitulé INDICATEURS (abandon , traités par un agent etc
if ( Len(Trim([@2]))>0,[@2],peek(INDICATEURS)) as INDICATEURS,

//récupère le NOMBRE, le volume
[@7]as NOMBRE
Resident [Sheet1$];
drop table [Sheet1$];

NoConcatenate
Final:
Load
DATE_JOUR,
CAMPAGNE,
//crée un champ SDA clé jointure
Trim(left(Trim(mid([CAMPAGNE], 8 + 1)), 5)) AS [SDA],
TYPE,
//if(wildmatch(INDICATEURS,'*Raccrochés*'),'Raccroché avant file attente',INDICATEURS) as INDICATEURS,
if(wildmatch(INDICATEURS,'*Raccrochés*'),'SVI',INDICATEURS) as INDICATEURS,
NOMBRE

resident Temp
// tri au final par indicateurs et si colone nombre = chiffres et supprime les lignes appels+ rappels
where wildmatch([INDICATEURS],'Cloturés','Débordement','Reroutés','Ignorés','*Raccrochés*','Abandon','Traités par un agent')
and isnum(NOMBRE) and NOMBRE >=0 and TYPE <>'Appels + Rappels';
drop table Temp;

 

1 Solution

Accepted Solutions
dilipranjith
Honored Contributor

Re: incremental load , multiple excel file in the same folder

Step 1 - Set up the csv file load (i.e. load only the new files

Basically for the first load (based on a flag) load the file names and data into a qvds

Subsequent loads

- get list of files names

- load old list of file names from qvd

- create another table with latest filenames only

- load data from qvd

- loop through new file names and load which gets autoconcatenated

- drop unnecessary tables (havent done this in the app to prove it works)

- save qvds

see below script

 

Step 2 - implement incremental load (update and/or) delete on the latest loads. check below

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

 

 

set vRoot = '.';
set vFirstLoad=0; //set 1 for first reload

FOR Each File in filelist ('$(vRoot)'&'\*.xlsx')
	CurrentFileList:
	Load 
		'$(File)' as File		
		autogenerate 1;	 	
next File
if(vFirstLoad=1) then
	MyTable:
	LOAD A, 
	     B, 
	     C
	FROM
	[./*.xlsx]
	(ooxml, no labels, table is data);
else 
	
	
	OldFileList:
	LOAD File as History
	FROM
	[.\FileList.qvd]
	(qvd);
	
	NoConcatenate
	NewFiles:
	Load
		 File as NewFile
		resident CurrentFileList
		 Where not exists(History, File);
	
	
	
	MyTable:
	LOAD A, 
	     B, 
	     C
	FROM [.\MyTable.qvd]
	(qvd);
	
	FOR EACH File in FieldValueList('NewFile')		
		LOAD A, 
		     B, 
		     C
		FROM
		[$(File)]
		(ooxml, no labels, table is data);
	next;
	
END IF
STORE CurrentFileList into FileList.qvd;
STORE MyTable into MyTable.qvd;
drop table CurrentFileList;
6 Replies
dilipranjith
Honored Contributor

Re: incremental load , multiple excel file in the same folder

Step 1 - Set up the csv file load (i.e. load only the new files

Basically for the first load (based on a flag) load the file names and data into a qvds

Subsequent loads

- get list of files names

- load old list of file names from qvd

- create another table with latest filenames only

- load data from qvd

- loop through new file names and load which gets autoconcatenated

- drop unnecessary tables (havent done this in the app to prove it works)

- save qvds

see below script

 

Step 2 - implement incremental load (update and/or) delete on the latest loads. check below

https://www.analyticsvidhya.com/blog/2014/09/qlikview-incremental-load/

 

 

set vRoot = '.';
set vFirstLoad=0; //set 1 for first reload

FOR Each File in filelist ('$(vRoot)'&'\*.xlsx')
	CurrentFileList:
	Load 
		'$(File)' as File		
		autogenerate 1;	 	
next File
if(vFirstLoad=1) then
	MyTable:
	LOAD A, 
	     B, 
	     C
	FROM
	[./*.xlsx]
	(ooxml, no labels, table is data);
else 
	
	
	OldFileList:
	LOAD File as History
	FROM
	[.\FileList.qvd]
	(qvd);
	
	NoConcatenate
	NewFiles:
	Load
		 File as NewFile
		resident CurrentFileList
		 Where not exists(History, File);
	
	
	
	MyTable:
	LOAD A, 
	     B, 
	     C
	FROM [.\MyTable.qvd]
	(qvd);
	
	FOR EACH File in FieldValueList('NewFile')		
		LOAD A, 
		     B, 
		     C
		FROM
		[$(File)]
		(ooxml, no labels, table is data);
	next;
	
END IF
STORE CurrentFileList into FileList.qvd;
STORE MyTable into MyTable.qvd;
drop table CurrentFileList;
brunobertels
Valued Contributor II

Re: incremental load , multiple excel file in the same folder

Hi 

Thanks first for your help.

I tried to test your script but i have an error message 'Table CurrentFileList' Not Found 

And i don't know what was wrong 

 

But i finally found the solution  thanks again Dilipranjith it works perfectly

 Bruno

 

IntrumASN
New Contributor

Re: incremental load , multiple excel file in the same folder

I too get the 'Table CurrentFileList' Not Found. May I know how you solved it?

brunobertels
Valued Contributor II

Re: incremental load , multiple excel file in the same folder

Hi 

i wrote directly  the path rather than use variable : 

 

so insteed of : 

set vRoot = '.';
set vFirstLoad=0; //set 1 for first reload

FOR Each File in filelist ('$(vRoot)'&'\*.xlsx')
	CurrentFileList:
	Load 
		'$(File)' as File		
		autogenerate 1;	 

 I worte this for me 

FOR Each File in filelist ('[lib://DAILY/*PROD*.xls]')

ListeFile_PROD:
Load
'$(File)' as File
autogenerate 1;

 

hope it helps 

dilipranjith
Honored Contributor

Re: incremental load , multiple excel file in the same folder

The code i copy pasted was something i created for qlikview. Where a variable made sense 🙂
with sense the connection will replace it 🙂
IntrumASN
New Contributor

Re: incremental load , multiple excel file in the same folder

Thank you both for taking the time to reply :smileyhappy: