topic Re: Loading Latest File From A Folder Into Qlikview in QlikView

Loading Latest File From A Folder Into Qlikview

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

Dear Qlikview Experts,

I had came across a weird situation whereby I was unable to load the latest file from a folder into my Qlikview Dashboard. My excel files inside the folder were look like below: (The date format is MM-DD-YYYY)

The script I was using currently is: (But it keeps getting 12-31-2017Testing as the latest file)

SET ErrorMode = 0;

Set vPath2 = 'C:\Users\QianNing\Desktop\3In1\ABB';
Let vMaxDate2 = 0;

For Each File2 in FileList(vPath2 &'\??-??-????Testing.xls')
Let vDate2 = Date#(Left(File, 10), 'MM-dd-yyyy');

If vDate2 > vMaxDate2 Then
Let vMaxDate2 = vDate2;
Let vMaxFile2 = File2;
End If
Next

t1:
Load date

FROM
[$(File2)]
(biff, embedded labels, table is A_FINAL$);

Couldn't figure out where went wrong and appreciate your time for the help! I had attach my folder (With files) and qvw to ease your reference.

Best Regards

QianNing

Re: Loading Latest File From A Folder Into Qlikview

vishalarote — Fri, 18 May 2018 05:40:21 GMT

Are u want letest file it means ur maxdate file like 05-08-2018Testing?

Re: Loading Latest File From A Folder Into Qlikview

s10157754 — Fri, 18 May 2018 05:55:00 GMT

Dear Vishal,

Yes. The latest file I would like to get is : 05-08-2018Testing.

Best Regards

Re: Loading Latest File From A Folder Into Qlikview

kaushiknsolanki — Fri, 18 May 2018 05:57:32 GMT

Try this.

SET ErrorMode = 0;

Set vPath2 = 'C:\Users\Kaushik\Downloads\ABB\ABB';

Let vMaxDate2 = 0;

For Each File2 in FileList(vPath2 &'\*testing.xls')

Let vDate2 = Date(Date#(Left(right(File2,21), 10), 'MM-dd-yyyy'));

If vDate2 > vMaxDate2 Then

Let vMaxDate2 = vDate2;

Let vMaxFile2 = File2;

End If

t1:

Load date

FROM

[$(vMaxFile2)]

(biff, embedded labels, table is A_FINAL$);

Regards,

Kaushik Solanki

Re: Loading Latest File From A Folder Into Qlikview

s10157754 — Fri, 18 May 2018 06:10:44 GMT

Dear Kaushik,

Thank you so much for pointing out my mistakes over date function. Your solution works like a charm! Have a good day!

Best Regards

QianNing

Re: Loading Latest File From A Folder Into Qlikview

vishalarote — Fri, 18 May 2018 06:22:11 GMT

Nice one Kaushik...

Instead of left and right function use mid(File2,1,10) this will also take perfect date.

Best Regards,

Vishal Arote.

Re: Loading Latest File From A Folder Into Qlikview

jonathandienst — Fri, 18 May 2018 06:26:16 GMT

If you use SET ErrorMode = 0, then don't forget to reset the error mode to 1!

Re: Loading Latest File From A Folder Into Qlikview

s10157754 — Fri, 18 May 2018 06:30:54 GMT

Dear Jonathan,

Do you mean I need to set ErrorMode = 1 at the end of my entire script? What will happen if I did not reset?

Best Regards

QianNing