topic Re: How do you load a variable value as a field value in the load statement? in QlikView

How do you load a variable value as a field value in the load statement?

Thu, 29 Aug 2013 19:24:40 GMT

I have multiple files (.xls) in a folder that I pull data from (e.g. File012013.xls, File022013.xls, File032013.xls, etc)

As you would guess, when I pull data from these file in a loop, I would like to use the numeric part of the file to use populate the month and the year field in the load statement. My code is below. I'm getting an error stating that it cannot find the field name <File012013.xls> when loading.

Table1:

LOAD * Inline

[field1,field2, F_Month, F_Year];

FOR Each file in FileList('$(vSourcedirectory)File*.xls')

Concatenate(Table1)

LOAD

field1,

field2,

Left(Right(Text($(file)), 10), 2) as F_Month,

Left(Right(Text($(file)), 8), 4) as F_Year

From [$(file)]

(biff, embedded labels, table is Sheet1$);

On another note, I get similar message when I try to put a declared variable as a Text($(var)) as SomeField, I get similar message.

Any help is appreciated. Thanks in advance.

Re: How do you load a variable value as a field value in the load statement?

Clever_Anjos — Thu, 29 Aug 2013 19:41:04 GMT

Just add quotes

LOAD

field1,

field2,

Left(Right(Text('$(file)'), 10), 2) as F_Month,

Left(Right(Text('$(file)'), 8), 4) as F_Year

From [$(file)]

(biff, embedded labels, table is Sheet1$);

Re: How do you load a variable value as a field value in the load statement?

Thu, 29 Aug 2013 19:56:44 GMT

Thanks for quick response. This was driving me crazy!

Re: How do you load a variable value as a field value in the load statement?

whiteline — Thu, 29 Aug 2013 20:03:17 GMT

Hi.

You should add quotes, since dollar-sign expantion just replaces exactly $(file) with a file name.

Left(Right(Text('$(file)'), 10), 2) as F_Month,

Left(Right(Text('$(file)'), 8), 4) as F_Year