For each in Filelist - only selects the first file

[arixooo123]

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

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

[arixooo123]

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]

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

[arixooo123]

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

[arixooo123]

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]

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 · Accepted solution]

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.