incremental load , multiple excel file in the same folder

[brunobertels]

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;

[dplr-rn · Accepted solution]

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]

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]

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

[brunobertels]

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 

[dplr-rn]

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

[IntrumASN]

Thank you both for taking the time to reply 

[Adorable09]

Hi @dplr-rn ,

Thanks for the above solution.

if(vFirstLoad=1) then
	MyTable:
	LOAD A, 
	     B, 
	     C
	FROM
	[./*.xlsx]

I am trying to understand that if the above code (pasted) is loading the data from all the excel files in the folder during full reload to load history data into qvd?How this syntax "./*.xlsx" is working ?or Do we need to add for  ..next syntax to load all the files from the folder?

[brunobertels]

hi 

you don't need "for next" syntax as the star * force qlik to load all the file with .xlsx extension present in the folder. 

For me as i have many different excel file in the same folder I used *PROD*.xls  to force qlik to load only files with partial name PROD in it. 

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

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

full name looks like 2021-08-16_PROD_ACTIVITY1.xls 

hope it helps 

Bruno