Loop through fields in column to assign as a variable for file load

[Anonymous]

Hello,

I have a list of dates that are used to identify the extensions of named files QVD.

I would like to look through the list, setting the value as a variable and then load that QVD data.

Then move to the next on the list and load the QVD.  Currently I have to hard code an extension every month.

With the below data, the variable remains as null.

//Example of table with fields to set as variables

PeriodEnd //title of Field in table CalendarFields

1/2/2016

2/6/2016

3/5/2016

//etc.

let vNumRows =NoOfRows('CalendarFields');

//Loop to Load

For i = 0 to $(vNumRows)

  let vFile1 = [PeriodEnd];

  Concatenate //Please note I have a table already started.

  Load

    Field1,

     Field2,

     Field 3

  FROM

  [\\ABC\Data\$(vFile1).qvd]

  (qvd);

Next

The error I get is that the $(vFile1) never populates with a value because the vFile1 is always <null>.

So need need $(vFile1) to = 1/2/2016

then $(vFile1) = 2/6/2016

then $(vFile1) = 3/5/2016....etc,

[settu_periasamy · Accepted solution]

Hi,

Try with Peek function to set the values Like

Let vFile1=Peek(PeriodEnd,i,'TableName');

then (try without single quote in the Loading table, otherwise put single quote(

Load

    Field1,

     Field2,

     Field 3

  FROM

  [\\ABC\Data\'$(vFile1)'.qvd]

  (qvd);

[Anonymous]

Thank you Settu

Here is the final syntax that worked.

For i = 0 to $(vNumRows)

let vsumbox = (i+1); //Row zero did not return a value.

Let vBox =  floor(date(FieldValue('PeriodEnd',$(vsumbox))));  //I Ioaded and uniquely titled the field PeriodEnd so no table required.

[\\ABC\Data\$(vFile1).qvd] //No single quotes.

I'm currently on version 12.0.20400.00 64 bit

This will save so much time!