Use variables in Load expression

[#1]

LEGEND:

"tempXlsPlanChart" is a Qlikview table imported table from Excel.

"tempXlsPlanChartColumn" is a Qlikview table imported from SQL server ,consists of "tempXlsPlanChart" columns with some attributes.  "ClmnXls" is the desired one.

I'm trying to create new Qlikview table within the script editor that loads from "tempXlsPlanChart " only the columns that are specified in "tempXlsPlanChartColumn" table.

SCRIPT :

//Concat delivers the column names (as one string with comma delimiter) from tempXlsPlanChart to vStr variable

SET vStr=Concat(ClmnXls, ', ');

Table:

//the vStr variable expends to its string value and creates the column names in the Load statement.

LOAD $(vStr)

RESIDENT tempXlsPlanChart;

DROP TABLE tempXlsPlanChart;

PROBLEM :

When the compiler runs it doesn't expand the variable in the LOAD statement, and sends error message.

Does some one have a clue how to solve this problem ?

[alexandros17]

Try with

LOAD $(=vStr)

or

LOAD $(=$(vStr))

let me know

[#3]

Fast replied!

But unfortunately the compiler send Syntax Error message,

This time the variable expansion is null.

[ThornOfCrowns]

Make sure vStr doesn't have an extra comma on the end of the last fieldname.

[#5]

have you tried LET instead of SET ?

[Gysbert_Wassenaar]

SCRIPT :

//Concat delivers the column names (as one string with comma delimiter) from tempXlsPlanChart to vStr variable

SET vStr=Concat(ClmnXls, ', ');

The only thing this does is create a variable vStr with the value 'Concat(ClmnXls', ','). It does not create a variable with a comma separated list of column names. If you want that you need something like this:

Tmp:

load concat(ClmnXls, ',' ) as ColumnNames

Resident tempXlsPlanChartColumn;

LET vStr = peek('ColumnNames');

Drop Table Tmp;

Table:

Load $(vStr)

Resident tempXlsPlanChart;

[jonathandienst]

Hi

I think you need something like this:

....

ConcatFields:

LOAD Concat(ClmnXls, ',') As ConcatFields

RESIDENT tempXlsPlanChartColumn;

Let vStr = Peek('ConcatFields');

DROP Table ConcatFields;

Table:

LOAD $(vStr)

RESIDENT tempXlsPlanChart;

....

I am not sure if I understand your field names and table clearly, so you may need to adjust this to get it to work.

HTH

Jonathan

[#8]

The Concat function don't create extra comma.

[#9]

The compiler sends error message also when using LET function (variable expansion is null).

[#10]

The compiler doesn't execute the concat function it leaves it as : concat(distinct ClmnXls, ',' ). and therefore error message occurs.