Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Load excel file if exists

Hi,

Trying to figure this out.. would be great if someone can assist please. I suspect its probably where the 'if' is placed.

So the theres a folder created which is named a date every day, except on weekends and holidays. It is across countries so public holidays and weekends differ so i cant use weekday or weekend. I basically need to loop through all files in a folder directory, if that days folder exists, then import the file, if it doesnt move to the next day without raising an error in the script execution.

(im sorry its not colour code we have restrictions and i had to type all this out again)

THIS WORKS:

let varDays = num#(today() - date#('01/04/2015','DD/MM/YYYY'));

     FOR i=0 to varDays

          let vSuffix =  Date (today() - varDays + i, 'YYYY-MM-DD');

          let vSuffix2 =  Date(today() - varDays + i, 'YYMMDD');

           let vSuffix3 = Date(today() - varDays + i,'DD-MM-YYYY');

     DUMMYTABLE:

          LOAD MakeDate ($(vSuffix3),'DD-MM-YYYY') as Date,

                    Field1 as Field1

          from     k:\DUMMYFOLDER\$(vSuffix)\DUMMYFILE_$(vSuffix2).csv;

         

Trace $(i) - $(vSuffix);

NEXT i

I need to add this in to check if the folder exists:

If FileTime('k:\DUMMYFOLDER\$(vSuffix)) > 0 then

    RUN the CODE above

else ignore the error, increment the date and continue the loop....

2 Replies
tcullinane
Contributor II

Re: Load excel file if exists

Why not use dirlist() and filelist() and you don't need to create filenames

eg

for each Dir in dirlist('k:\Dummy\*')

for each File in filelist('$(Dir)')

Load something

from $(Dir)\$(File)

next File

next Dir

Employee
Employee

Re: Load excel file if exists

As I see it you need to put it around the LOAD, as vSuffix variables are updated each loop.

let varDays = num#(today() - date#('01/04/2015','DD/MM/YYYY'));

FOR i=0 to varDays

    let vSuffix =  Date (today() - varDays + i, 'YYYY-MM-DD');

    let vSuffix2 =  Date(today() - varDays + i, 'YYMMDD');

    let vSuffix3 = Date(today() - varDays + i,'DD-MM-YYYY');

    IF FileTime('k:\DUMMYFOLDER\$(vSuffix)) > 0 THEN

       DUMMYTABLE:

       LOAD MakeDate ($(vSuffix3),'DD-MM-YYYY') as Date,

            Field1 as Field1

       from    k:\DUMMYFOLDER\$(vSuffix)\DUMMYFILE_$(vSuffix2).csv;

    END IF  

    Trace $(i) - $(vSuffix);

NEXT i

Community Browser