How to LOAD data from a variable File Name ?

[#1]

Hello

I need too determine the data source file from a repository in wich there will be many source file with the same filename pattern. The only part of the name wich change is a block with the date yyyymm > fileNamePrefix_yyyymm_fileNameSuffix.ext

I think it could work load the file information with a loop

FOR each File IN filelist '../FOLDER/fileNamePrefix_*_fileNameSuffix.ext'

fileTable:

LOAD FileName('$(File)') as Filename,

     FileBasename() as FileBasename,

     FileExtension() as FileExtension,

     FileDir() as FileDir,

     //FilePath() as FilePath,

     FileSize('$(File)') as FileSize,

     FileTime('$(File)') as FileTime

     FROM '$(File)';

next File

then I wanted to determine the most recent file with a variable:

SET mySourceFile = MaxString(Filename);

data:

LOAD *

FROM [../FOLDER/$(mySourceFile)]

Wich doesn't work... even if I can get the right value of the variable mySourceFile displayed in a text Box.

I guess there is a way to evaluate the variable that I didn't understand... I tried a lot of option but didn't find the solution.

May someone help me to fix this little issue ???

best regards

Jerome

[swuehl · Accepted solution]

Unfortunately, you can't just use an aggregation function on a field like this.

You would need to use a LOAD statement and (most of the time) a GROUP BY clause.

Or you can sort your table descending and pick the first record:

FOR each File IN filelist '../FOLDER/fileNamePrefix_*_fileNameSuffix.ext'

fileTable:

LOAD FileName('$(File)') as Filename,

     FileBasename() as FileBasename,

     FileExtension() as FileExtension,

     FileDir() as FileDir,

     //FilePath() as FilePath,

     FileSize('$(File)') as FileSize,

     FileTime('$(File)') as FileTime

     FROM '$(File)';

next File

TMP:

LOAD Filename RESIDENT fileTable ORDER BY Filename desc;

        LET mySourceFile = peek('Filename',0,'TMP');

       DROP TABLE TMP;

data:

LOAD *

FROM [../FOLDER/$(mySourceFile)]

[#3]

it works !

thank you for your help !