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

Error in loading variable values in FOR loop

Hi Qlikview Experts,

I'm stuck in figuring out my mistake. Please help me find it out!

Source files are sent with date attached to their names to indicate data update day. Dates might be different for different files.

Goal:

    1. Find the latest files.

     2.  Store them in a table (stuck here   ).

Script:

META_DATA:

load

     FILE_REF_NO,

     FILE_PATH,

     BASE_FILE_NAME,

     DATE_SUFFIX,

     FILE_EXT,

     FULL_FILE_NAME,

     TABLE_NAME,

     Explanation,

     RECORD_SIZE

FROM

.xlsx]

(ooxml, embedded labels, table is Sheet4)

;

Let vNumTables = peek('FILE_REF_NO',-1,'META_DATA');

For t = 0 to $(vNumTables)-1

  Let vTable = peek('TABLE_NAME',$(t),'META_DATA');

  Let vFileName = peek('BASE_FILE_NAME',$(t),'META_DATA');

  Let vFileExt = peek('FILE_EXT',$(t),'META_DATA');

  let vRecordSize = peek('RECORD_SIZE',$(t),'META_DATA');

  FOR each File IN filelist ('$(vSourcePath)$(vFileName)_*')

  fileTable:

  LOAD

    '$(File)' as FullFileName,

     Subfield(Subfield('$(File)', '\', SubstringCount('$(File)', '\') + 1), '.', 1) as FileBase,

     Subfield(Subfield(Subfield('$(File)', '\', SubstringCount('$(File)', '\') + 1), '.', 1),'_',2) as FileDate,

     FileTime('$(File)') as FileTime

     autogenerate 1;

     ;

    next File;

  TMP:

    LOAD FullFileName, FileDate RESIDENT fileTable ORDER BY FileDate desc;

  

    drop Table fileTable;

        LET vFiletoLoad = peek('FullFileName',0,'TMP');

        Let vCurFileDate =date#(peek('FileDate',0,'TMP'), 'YYYYMMDD');

        Let  vLastFileDate =date#(peek('FileDate',1,'TMP'), 'YYYYMMDD');

    DROP TABLE TMP;

  

    NEW_MD:

    load  $(t) as FILE_NUM,

    $(vFiletoLoad) as LAST_FILE

         AutoGenerate 1;

               

    next t;       

Errors (See attached screenshot):

1. Syntax error - 'FROM not found' , NEW_MD shown twice for each t.

2. When there are no files in a directory - fileTable is not populated, TMP is not found. How to handle this case?

Thank you for your help!

1 Solution

Accepted Solutions
MVP
MVP

Re: Error in loading variable values in FOR loop

Try to enclose the vFileToLoad variable expansion in single quotes:

'$(vFiletoLoad)' as LAST_FILE

2 Replies
MVP
MVP

Re: Error in loading variable values in FOR loop

Try to enclose the vFileToLoad variable expansion in single quotes:

'$(vFiletoLoad)' as LAST_FILE

Not applicable

Re: Error in loading variable values in FOR loop

Thank you Swuehl!

It works! I also added check for number of records in fileTable and got rid from annoying errors

Community Browser