Load Latest file from a folder into Qlikview

[s10157754]

Dear Qlikview Experts,

I had tried to work around on loading the latest excel file from a folder into Qlikview, but my code didn't work. May I know where my mistake is? Thanks for your help in advance!

Set vPath = 'C:\Users\Okawa\Desktop\GE'; 

For each FileExtension in 'xls' 
For each File in FileList(vPath &'\??-??-????GE.'& FileExtension) 

Let vMaxDate=Peek(Date(Date#(left(FileBaseName(),10),'MM-DD-YYYY'),'MM-DD-YYYY'));

If Date(Date#(left(FileBaseName(),10),'MM-DD-YYYY'),'MM-DD-YYYY') = '$(vMaxDate)' then 

for each Sheet in 'BC_227','BC_226','BC_225'

Data:

LOAD @1 as Date,
@2 as Time,
@5 as Result,


SubField('$(Sheet)','_',2) as BCNo, 
Day(@1)  as Day,
Month(@1)  as Month,
Year(@1)  as Year

FROM
[$(File)]
(biff, no labels, table is $(Sheet)$);

Next

ENDIF

NEXT

[jonathandienst · Accepted solution]

You need two steps - first scan the folder to find the most recent file, then load from that file

Set vPath = 'C:\Users\Okawa\Desktop\GE'; 

Let vMaxDate = 0;

// Scan file to finf most recent date

For Each File in FileList(vPath &'\??-??-????GE.xls') 

  Let vDate = Date#(Left(File, 10), 'MM-dd-yyyy');

  If vDate > vMaxDate Then

    Let vMaxDate = vDate;

    Let vMaxFile = File;

  End If

Next

// Load the sheet from the most recent file

For Each Sheet in 'BC_227','BC_226','BC_225'

  Data:

    LOAD @1 as Date,

    @2 as Time,

    @5 as Result,

    SubField('$(Sheet)','_',2) as BCNo, 

    Day(@1)  as Day,

    Month(@1)  as Month,

    Year(@1)  as Year

  FROM [$(vMaxFile)] (biff, no labels, table is $(Sheet)$);

Next

[s10157754]

Dear Jonathan,

Thanks for your prompt reply. However, your code is loading the oldest file but not the latest file. May I know how to modify your code so that I can load the latest file? Thank you for your help!

Best Regards

[jonathandienst]

Are you sure that you are referencing the correct variable in the last load statement:

For Each Sheet in 'BC_227','BC_226','BC_225'

  Data:

    LOAD @1 as Date,

    @2 as Time,

    @5 as Result,

    SubField('$(Sheet)','_',2) as BCNo,

    Day(@1)  as Day,

    Month(@1)  as Month,

    Year(@1)  as Year

  FROM [$(vMaxFile)] (biff, no labels, table is $(Sheet)$);

Next

[s10157754]

Dear Jonathan,

It was still loading the oldest file.

FROM [$(vMaxFile)] (biff, no labels, table is $(Sheet)$);

FROM [$(File)] (biff, no labels, table is $(Sheet)$);

After I changed 'vMaxFile' to 'File' and it finally load the latest file. Thank you so much for your help!

Best Regards

[vikraant]

Hi Zhang ,

I used Jonathan's code with a minor change for the files you had provided. Instead of Left(  File, 10), I used Left( File, len(File)  -6 )  . It is now fetching the latest file. Please try out the following code

Set vPath = '.';

Let vMaxDate = 0;

// Scan file to finf most recent date

For Each File in FileList(vPath &'\??-??-????GE.xls')

  Let vDate = Date#(Left(File,  Len(File) - 6), 'MM-dd-yyyy');

  If vDate > vMaxDate Then

    Let vMaxDate = vDate;

    Let vMaxFile = File;

  End If

Next

// Load the sheet from the most recent file

For Each Sheet in 'BC_227','BC_226','BC_225'

  Data:

    LOAD @1 as Date,

    @2 as Time,

    @5 as Result,

    SubField('$(Sheet)','_',2) as BCNo,

    Day(@1)  as Day,

    Month(@1)  as Month,

    Year(@1)  as Year,
   
    FileName()  as Test
  FROM [$(vMaxFile)] (biff, no labels, table is $(Sheet)$);

Next

[chandnishah]

@vikraant, @jonathandienst I have a question in below code : Why this file names are hard coded in this line. I didn't understand this line

For Each Sheet in 'BC_227','BC_226','BC_225'

Can someone please explain this.

Thanks,

Chandni