Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Next
t1:
Load date
FROM
[$(vMaxFile2)]
(biff, embedded labels, table is A_FINAL$);
Regards,
Kaushik Solanki
Are u want letest file it means ur maxdate file like 05-08-2018Testing?
Dear Vishal,
Yes. The latest file I would like to get is : 05-08-2018Testing.
Best Regards
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
Next
t1:
Load date
FROM
[$(vMaxFile2)]
(biff, embedded labels, table is A_FINAL$);
Regards,
Kaushik Solanki
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
Nice one Kaushik...
Instead of left and right function use mid(File2,1,10) this will also take perfect date.
Best Regards,
Vishal Arote.
If you use SET ErrorMode = 0, then don't forget to reset the error mode to 1!
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