Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)]
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)]
it works !
thank you for your help !