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: 
s10157754
Creator III
Creator III

Load Latest file from a folder into Qlikview

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(@1as Day,
Month(@1as Month,
Year(@1as Year

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

Next

ENDIF

NEXT

 

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

6 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
s10157754
Creator III
Creator III
Author

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
Partner - Champion III
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
s10157754
Creator III
Creator III
Author

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
Creator
Creator

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
Contributor II
Contributor II

@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