Variables Not Working In A For Loop

[#1]

I have been trying to loop through a collection of qvd files to extract field data from them.  However, I keep getting the following error:  "Syntax error, missing/misplaced FROM: QVD_METADATA:"  In the script body displayed in the error message, I also see that the variables have vanished from the places where I placed them in the script (not in the script itself, just in the script body displayed in the error message).

It appears the variables are not being created or assigned values.  But, I can't see where the problem lies.  Does anyone have any experience with this?  Here is the code I am using:

QVD_DATA:

LOAD

   *

  , Date(Today(), 'DD-MMM-YYYY') AS UPDATE_DATE_QVD_METADATASET

;

LOAD

  [QV File Name],

     [QVD Full Path]

FROM

$(vInputFile1)

(ooxml, embedded labels, table is [Data Links]);

Let vRowNum = NoOfRows('QVD_DATA'); /*Get total number of rows for the loop to do its thing*/

For i = 1 To $(vRowNum)

  Let vQvdPath = FieldValue([QVD Full Path],$(i)); /*Assign each qvd file path to a variable to be used as the path in the metadata extraction load*/

  Let vFileName = FieldValue([QV File Name],$(i));

  QVD_METADATA:

  LOAD

     1                      as FieldCount,

     $(vFileName)   as [File Name],

     FieldName              as [Field Name],

     BitOffset              as [Bit Offset],

     BitWidth               as [Bit Width],

     Bias                   as [Bias],

     NoOfSymbols            as [No Of Symbols],

     [NumberFormat/Type]    as Type,

     [NumberFormat/nDec]    as Dec,

     [NumberFormat/UseThou] as UseThou

  FROM $(vQvdPath) (XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader]);

Next i

[#2]

Thanks for the suggestion, Peter.  I tried adding the quotes as suggested, but no dice.  The script error is still thrown and the two variables are still evaluating to null when I step through the script.

[Peter_Cammaert]

Did you remove the square brackets from the field names?

The error message is generated because the $(vFileName) value for column [File Name] evaluates to nothing, and an illegal LOAD Column definition syntax will almost always lead to a message about a missing FROM clause. The same happens when you put one comma too many at the end of the column list.or you forget an AS keyword.

Best,

Peter

[vinieme12]

try using peek() instead of fieldvalue

peek(fieldname [ , row [ , tablename ] ] )

[vinieme12]

example:

Let vQvdPath = PEEK('QVD Full Path',$(i),'QVD_DATA');

  Let vFileName = PEEK('QV File Name',$(i),'QVD_DATA');

[Peter_Cammaert]

Good thinking by Vineeth.

FieldValue returns Distinct values, so there will probably be fewer than the number of table rows. If all QVD files reside in the same path, there will be only one FieldValue for field vQvdPath.

See the same help text from above.

Best,

Peter

[#7]

Thanks so much for your help, Vineeth and Peter!  Ultimately, I had to redo the script quite a bit and adopt a different approach.  However, now it works!  Here is how the code looks, in case anyone needs to extract metadata from QVDs and might find this useful:

QVD_DATA:

LOAD

   *

  , Date(Today(), 'DD-MMM-YYYY') AS UPDATE_DATE_QVD_METADATASET

;

LOAD

  [QV File Name],

     [QVD Full Path],

     RecNo() AS [Data Row]

FROM

$(vInputFile1)

(ooxml, embedded labels, table is [Data Links])

;

NoConcatenate

QVD_END:

LOAD Distinct

MAX([Data Row]) AS [Data Row]

Resident QVD_DATA

;

Let vNumberQVD=Num(Peek('Data Row',0,'QVD_END')); /*Get total number of rows for the loop to do its thing*/

Let vNumberStart=1;

Drop Table QVD_END;

Do While vNumberStart<=vNumberQVD

  NoConcatenate

  PATH:

  Load

  *

  Resident QVD_DATA

  Where [Data Row]=$(vNumberStart)

  ;

  Let vPath='['&Peek('QVD Full Path',0,'PATH')&'] ';

  Let vFileName=Peek('QV File Name',0,'PATH');

  Drop Table PATH;

  Let vNumberStart=$(vNumberStart)+1;

  // Let vQvdPath = FieldValue('[QVD Full Path]',$(i)); /*Assign each qvd file path to a variable to be used as the path in the metadata extraction load*/

  // Let vFileName = FieldValue('[QV File Name]',$(i));

  QVD_METADATA:

  LOAD

     1                      as FieldCount,

     '$(vFileName)'   as [QV File Name],

     FieldName              as [Field Name],

     BitOffset              as [Bit Offset],

     BitWidth               as [Bit Width],

     Bias                   as [Bias],

     NoOfSymbols            as [No Of Symbols],

     [NumberFormat/Type]    as Type,

     [NumberFormat/nDec]    as Dec,

     [NumberFormat/UseThou] as UseThou

  FROM $(vPath)(XmlSimple, Table is [QvdTableHeader/Fields/QvdFieldHeader])

  ;

  QVD_TABLE_INFO:

     LOAD

          1                      as QVDCount,

          '$(vFileName)'         as [File Name],

          QvBuildNo              as [QV Build No],

          CreatorDoc             as [QVD Creator],

          CreateUtcTime          as [Time Created],

          SourceFileSize         as [Source File Size],

          TableName              as [Table Name],

          RecordByteSize         as [Record Byte Size],

          NoOfRecords            as [Number Of Records]

     FROM $(vPath) (XmlSimple, Table is [QvdTableHeader]);

Loop;

[#8]

Actually, the QVD_TABLE_INFO load should be removed from that code above.  A Cartesian join results otherwise.