Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
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

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

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

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

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