Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Guys,
I appreciate if you help me solve this issue,
I am trying to fetch a list of qvd file names, then based on the date (YYYY-MMM) in their names, only select the last one (the most recent one)
Table names are like:
MyTable_2017-Jan
MyTable_2016-Dec
,...
So far I'm here:
for each vFile in FileList('lib://QlikData/*.qvd')
Files:
LOAD *, FileBaseName() as FileName
From $(vFile);
Let vMaxFile=Max(Date((SubField(FileName,'_',-1)),'YYYY-MMM'));
Load * from ['lib://QlikData/TsbleName_$(vMaxFile).qvd'](qvd);
I am expecting this to load the last qvd, but it doesn't
If you want to select the file based on the year/month in the filename, then you can do this:
vMaxDate = 0;
vFinalFile ='';
For Each vFile in FileList('lib://QlikData/*.qvd')
vDate = Date#(TextBetween(vFile, 'Table_', '.qvd'), 'yyyy-MMM');
vFinalFile = If(vDate > vMaxDate, vFile, vFinalFile);
vMaxDate = RangeMax(vMaxDate, vDate);
Next
Consignments:
Load * from [$(vFinalFile)] (qvd);
Set vMaxDate =;
Set vFile =;
Set vFinalFile =;
This is based on the details in your original post.
May be use
for each File in filelist ('lib://QlikData/*.qvd')
Files:
Load '$(File)' as Name,
FileTime( '$(File)' ) as FileTime
autogenerate 1;
next File
LatestFile:
first 1
Load
Name,
FileTime,
1 as dummy
Resident Files
Order By FileTime DESC;
drop table Filed;
Thanks Anil,
But I really need to use the date in the fileName. The modified date of these files are not reliable and I need to use that date later in my code.
Isn't there any way to resolve that ?
Just to test, I used your script as below :
for each vFile in FileList('lib://QlikData/*.qvd')
Files:
Load '$(vFile)' as FName,
FileTime( '$(vFile)' ) as FileTime
autogenerate 1;
next vFile
LatestFile:
first 1
Load
FName,
FileTime,
1 as dummy
Resident Files
Order By FileTime DESC;
drop table Files;
Let vMaxFile=FName;
Consignments:
Load * from [lib://QlikData/'$(vMaxFile)'](qvd);
It doesn't load any file, the error is :
The following error occurred:
Cannot open file: 'lib://QlikData/'''
It's clearly telling related your files are not located in QlikData folder. Can you cross check that and connect manually those filed by help of guide then have a look and consider my script to get latest one
Anli,
I checked the logs, it reads all the files and pick the maximum, but on below step, it fails to load it with a variable
I think the issue is there..do you have any suggestion?
Load * from [lib://QlikData/$(vMaxFile)](qvd);
May be use NoConcatenate on your last statement like below
Consignments:
NoConcatenate
Load * from [lib://QlikData/'$(vMaxFile)'](qvd);
Or
remove the single colon on your variable while loads statement like below
Load * from [lib://QlikData/$(vMaxFile)](qvd);
So you think, normally it's ok to use variables inside the lib address?
This is the first time that I want to load that qvd file and using NoConcatenate doesn't change anything, I tried
I guess I realized what the issue is,
when using for each vFile, it actually stores the whole link into this variable.
For instance, vFile becomes lib://QlikData/MyTable_2016-Aug.qvd
But it should only contain "MyTable_2016-Aug.qvd"
do you have any suggestion?
If you want to select the file based on the year/month in the filename, then you can do this:
vMaxDate = 0;
vFinalFile ='';
For Each vFile in FileList('lib://QlikData/*.qvd')
vDate = Date#(TextBetween(vFile, 'Table_', '.qvd'), 'yyyy-MMM');
vFinalFile = If(vDate > vMaxDate, vFile, vFinalFile);
vMaxDate = RangeMax(vMaxDate, vDate);
Next
Consignments:
Load * from [$(vFinalFile)] (qvd);
Set vMaxDate =;
Set vFile =;
Set vFinalFile =;
This is based on the details in your original post.