topic Re: Load Latest file from a folder into Qlikview in QlikView

Load Latest file from a folder into Qlikview

s10157754 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Load Latest file from a folder into Qlikview

jonathandienst — Fri, 09 Feb 2018 05:17:00 GMT

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

Re: Load Latest file from a folder into Qlikview

s10157754 — Fri, 09 Feb 2018 05:28:16 GMT

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

Re: Load Latest file from a folder into Qlikview

jonathandienst — Fri, 09 Feb 2018 07:15:27 GMT

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

Re: Load Latest file from a folder into Qlikview

s10157754 — Fri, 09 Feb 2018 08:19:51 GMT

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

Re: Load Latest file from a folder into Qlikview

vikraant — Fri, 09 Feb 2018 11:36:13 GMT

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

// 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)$);

Re: Load Latest file from a folder into Qlikview

chandnishah — Thu, 10 Jan 2019 14:56:27 GMT

@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