Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

How to LOAD data from a variable File Name ?

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

1 Solution

Accepted Solutions
MVP
MVP

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

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

2 Replies
MVP
MVP

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

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

Not applicable

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

it works !

thank you for your help !

Community Browser