2 Replies Latest reply: Mar 23, 2016 12:54 PM by Steve Lord RSS

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

    Steve Lord

      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

      [T:\Incentive\_REDEMPTION\OutBound\datafeed_*.xlsx]

      (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

      [C:\Users\steve\Desktop\filetimes.qvd]

      (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!

        • Re: help with let vmostcurrentfile = lookup ('filename','tablename')
          Bill Markham

          Maybe try something like :

           

               Let vMostCurrentFile = Peek('mostcurrentfile')  ;

            • Re: help with let vmostcurrentfile = lookup ('filename','tablename')
              Steve Lord

              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

              [T:\Incentive\_REDEMPTION\OutBound\*.xlsx]

              (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

              [C:\Users\steve\Desktop\filetimes.qvd]

              (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);