topic Re: Loop through fields in column to assign as a variable for file load in QlikView

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

Anonymous — Wed, 21 Jun 2017 19:02:49 GMT

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

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,

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

settu_periasamy — Thu, 22 Jun 2017 05:00:21 GMT

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

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

Anonymous — Thu, 22 Jun 2017 19:33:45 GMT

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!