topic How to LOAD data from a variable File Name ? in QlikView

How to LOAD data from a variable File Name ?

Mon, 18 Nov 2013 18:06:03 GMT

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

Re: How to LOAD data from a variable File Name ?

swuehl — Mon, 18 Nov 2013 18:22:56 GMT

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

Re: How to LOAD data from a variable File Name ?

Mon, 18 Nov 2013 18:41:53 GMT

it works !

thank you for your help !