Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ced_foning
Contributor III
Contributor III

Fetch data scripting

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) : 

Capture.PNG

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

Labels (3)
1 Solution

Accepted Solutions
Taoufiq_Zarra

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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

10 Replies
Taoufiq_Zarra

One solution :

let's assume that I have these files :

Capture.PNG

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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
ced_foning
Contributor III
Contributor III
Author

Hello, it doesn't work. 

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

 

Thanks

 

Capture.PNG

 

Taoufiq_Zarra

Hi,

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
ced_foning
Contributor III
Contributor III
Author

See attached the old data

The data goes from 2014 to 2020

old.PNG

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?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
ced_foning
Contributor III
Contributor III
Author

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

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 :

Capture.PNG

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
ced_foning
Contributor III
Contributor III
Author

Capture.PNG

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 ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉