Fetch data scripting

[ced_foning]

Hello, i'm new in qlik and i have an issue. 

I have the following files. They are generated daily (the file of today is generated with the day of yerterday) : 

I want to have a script that can : 

Step 1 : If a file is created today, the file is fetched and stocked in a qvd file. Then for the next day, create another qvd file that will erase the one of the previous day. This will be done until the last day of the month

Step 2 When the file name reaches the end of the month,  it stores in a qvd file, and then restart STEP 1. 

NB : All the files with CVARiskReport.v5_XXXXXXXX.2.csv should not be taken into consideration

Thanks alot

[Taoufiq_Zarra]

One solution :

let's assume that I have these files :

The Script :

let path_AllesQVD = '.\Qlik_comm\';
let path_Alles = '.\Qlik_comm\*.csv';

for each File in filelist (path_Alles)

     let vQVDFileMonth=left(right('$(File)',29),23) ;
     let vToday=floor(Date(today()));
     let vEndMonth=floor(Date(Monthend(today())));
     let vFlag=index(left(right('$(File)',6),1),'.') ;
     
     
if $(vFlag)=0 then
	
	let FileDate=floor(Date(Date#(left(right('$(File)',12),8),'YYYYMMDD'))) ;
		
		if  $(FileDate)=$(vEndMonth) then
	     
		      Data:
		      
		      LOAD * FROM  $(File) (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
		      
			  Store Data into '$(path_AllesQVD)$(vQVDFileMonth).qvd' (qvd);
			 
			 drop table Data;
			 
	     elseif $(FileDate)=$(vToday) then
	 
	 
		      Data:
		      
		      LOAD * FROM  $(File) (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
		      
			  Store Data into '$(path_AllesQVD)$(vQVDFileMonth)_tmp.qvd' (qvd);
			 
			 drop table Data;
			 
		endif
	 
endif
	 
next File 

the output :

[ced_foning]

Hello, it doesn't work. 

I don't know why it searches for the oldest data, not the new ones. 

Thanks

[Taoufiq_Zarra]

Hi,

Can you share an example of the old file names->."..searches for the oldest data, not the new ones. " ?

[ced_foning]

See attached the old data

The data goes from 2014 to 2020

i want him to get only the newest file, and save it in QVD everday (CVARiskReport.v5_20200708.qvd )until the month goes by, then stores it in the format CVARiskReport.v5_202007.qvd

Thanks

[Taoufiq_Zarra]

The script sent:
1-ignore the file with *.2.csv
2- look in the folder for today's file, normally if you test the code today, it will look for the 20200710 file.
3-load the *.20200710 file in qvd_tmp
4-if we get to 20200731 it will create a 202007 file.

Can you confirm it's the senator you're looking for?

[ced_foning]

On your second point, here is the correction: 

2- look in the folder for today's file, normally if you test the code today, it will look for the 20200709 file.

if the test was done yesterday, it will look for the 20200708 file

3-load the *.20200709 file in qvd_tmp

[Taoufiq_Zarra · Accepted solution]

with this correction , the new Script :

let path_AllesQVD = '.\Qlik_comm\';
let path_Alles = '.\Qlik_comm\*.csv';

for each File in filelist (path_Alles)

     let vQVDFileMonth=left(right('$(File)',29),23) ;
     let vToday=floor(Date(today()))-1;
     let vEndMonth=floor(Date(Monthend(today())))-1;
     let vFlag=index(left(right('$(File)',6),1),'.') ;
     
     
if $(vFlag)=0 then
	
	let FileDate=floor(Date(Date#(left(right('$(File)',12),8),'YYYYMMDD'))) ;
		
		if  $(FileDate)=$(vEndMonth) then
	     
		      Data:
		      
		      LOAD * FROM  $(File) (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
		      
			  Store Data into '$(path_AllesQVD)$(vQVDFileMonth).qvd' (qvd);
			 
			 drop table Data;
			 
	     elseif $(FileDate)=$(vToday)  then
	 
	 
		      Data:
		      
		      LOAD * FROM  $(File) (txt, codepage is 1252, embedded labels, delimiter is '\t', msq);
		      
			  Store Data into '$(path_AllesQVD)$(vQVDFileMonth)_tmp.qvd' (qvd);
			 
			 drop table Data;
			 
		endif
	 
endif
	 
next File 

output :

[ced_foning]

Here is my log file

Every thing seems to be ok, but the qvd file tmp was not generated. 

Thanks for your help

[Taoufiq_Zarra]

according to the log, you only have files from 08/07/2020 when normally it should have the 09/07/2020 file
the tmp file is created from today's file so from 10/07 but according to the correction that you proposed it will search for the 09/07 file .

to test the script creates for example a file:  CVARiskReport.v5_20200709.csv ?