Fetch data script

[NiceC]

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

I have the following files : 

The qlik app is running each Friday. Each of the above files are generated daily. 

I want to have a script that can :

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

Step 2 : Get the last 15 end of month 

Step 3 : 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

i want him to get only the newest file of each friday, and save it in QVD everday (CVARiskReport.v5_20201013.qvd for example). When the month goes by, then stores it in the format CVARiskReport.v5_202010.qvd (getting the last work day of the month, and directly stores it in that format). 

here is what i've done but not working

let path_AllesQVD = '\\qlk-fim-prd\QLK_PAN_CUBES_INT\FIM\RWA_WEIGHT\qvd\CVAR';

let path_Alles = '\\qlk-fim-prd\QLK_PAN_IMPORT_INT\FIM\RWA_WEIGHT\CVAR\*.csv';

for each File in filelist (path_Alles)

let vQVDFileMonth=left(right('$(File)',29),23) ;
let vToday=floor(Date(today(), 'DD-MM-YYYY'))-1;
let vEndMonth=floor(Date(Monthend(today()), 'DD-MM-YYYY'))-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).qvd' (qvd);


drop table Data;

endif

endif

next File

See the log file attached

Thanks for your help

[marcus_sommer]

It looked that you have a logically issue within your conditions. This means you need to check if vEndMonth and FileDate are really suitable for your task.

Beside this it might be easier (especially in the long term) if you used a more appropriate folder-structure. Another way may be to load more/all files and to remove the unwanted data with a where-clause (of course it needs more resources but as long as you don't exceed the available time-frame it may the most practiable way).

- Marcus

[NiceC]

hi @marcus_sommer 

Thanks for your anwser. 
Could you help me out with some script ? 

Thanks

[marcus_sommer]

I think there is rather not an issue with your script (approach and syntax) else with the logic which variable-values are created and compared within the conditions. Quite helpful for such issues is often the use of TRACE statements like:

TRACE filedate: $(FileDate) = endmonth: $(vEndMonth) ;

This will be written within the log and also be visible within the progress-window from the load (which is usually more readable as the log).

- Marcus

[NiceC]

@marcus_sommer 

i'm having problems getting the last end of month files in that folder. files are generated every working day since 2019. What i want is to get the last working day of the last 15 months. 

thanks

[marcus_sommer · Accepted solution]

Instead of using monthend() which refers to calendar-dates try it with lastworkdate().

- Marcus