help with let vmostcurrentfile = lookup ('filename','tablename')

[stevelord]

Hi Community, I have script that loads filenames and filetimes, identifies the most current file, and makes a 1-line table with that file's filename and filetime.  I am a bit stuck on making the variable that populates with that filename and then putting that variable in the from statement.  The qlikview runs through all of the script below, then errors on the last table load showing the filepath and no filename in the error message.  I think I'm doing something wrong in the lookup() part of the let variable = lookup() line immediately before that last loadscript.

filetimes:

LOAD distinct filename() as filename,

     timestamp(filetime()) as filetime

FROM

(ooxml, embedded labels, table is Order);

left join Load distinct timestamp(max(filetime)) as maxfiletime resident filetimes;

Store filetimes into filetimes.qvd (qvd);

Drop table filetimes;

mostcurrentfile:

LOAD distinct

     filetime,

     maxfiletime,

     filename

FROM

(qvd) where filetime=maxfiletime; //this gets me to a 1-line table with the name of the most current file.

Let vMostCurrentFile = lookup('filetime','maxfiletime','filename','mostcurrentfile'); //I think I'm doing something wrong in here.

LOAD distinct

     OrderID,

     [Recipient Name],

     [External Id]

FROM

T:\Incentive\_REDEMPTION\OutBound\$(vMostCurrentFile) //syntax seems okay here, but error message shows 'T:\Incentive\_REDEMPTION\OutBound\' as the file I tried to select.

(ooxml, embedded labels, table is Order);

Thanks in advance!

[Anonymous · Accepted solution]

Maybe try something like :

     Let vMostCurrentFile = Peek('mostcurrentfile')  ;

[stevelord]

Worked like a charm.  Filezilla just went bye-bye,.

Final script I have is like this:

(I used the expanded peek syntax with the row designation and tablename for fun/future reference/peertraining in case we have to work with bigger tables for some reason)

filetimes: //collect filetime info from files in folder - since table is only a few fields it took under a minute to grab filetimes from hundreds of files - it can vary since it is technically handling all the rows in the files initially.  The remaining parts took less than a second..

LOAD distinct filename() as filename,

     timestamp(filetime()) as filetime,

     num(purgechar(left(filename(),28),'datafeed_-')) as filenum

FROM

(ooxml, embedded labels);

left join Load distinct timestamp(max(filetime)) as maxfiletime resident filetimes; //identify most current file - took a split second.

Store filetimes into filetimes.qvd (qvd); //save your new table

Drop table filetimes;

mostcurrentfile:

LOAD distinct filename, filenum, filetime, maxfiletime

FROM

(qvd) where filetime=maxfiletime; //load the name of the most current file - this is a 1-row table - took a split second.

LET vMostCurrentFile = Peek('filename',0,'mostcurrentfile'); //make a variable that equals this filename

LOAD *

FROM

T:\Incentive\_REDEMPTION\OutBound\$(vMostCurrentFile) //load ONLY that most current file - took a split second.

(ooxml, embedded labels);