Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
arixooo123
Creator III
Creator III

For each in Filelist - only selects the first file

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

1 Solution

Accepted Solutions
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

16 Replies
Anil_Babu_Samineni

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;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
arixooo123
Creator III
Creator III
Author

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 ?

arixooo123
Creator III
Creator III
Author

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/'''

Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
arixooo123
Creator III
Creator III
Author

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

Anil_Babu_Samineni

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
arixooo123
Creator III
Creator III
Author

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

arixooo123
Creator III
Creator III
Author

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?

jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein