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 ?

[#2]

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

[#3]

might be best to use an alias

debug it and publish what the value of the var is

keep trying different stuff

[#4]

I will be more specific ,

I think this has something to do with variables and functions :

This one works :

for i=0 to vRows-1

LET vStr= vStr & Peek('ClmnXls',i,'tempXlsPlanChartColumns') & ', ';

next

let vStr=left(vStr,len(vStr)-2);

temp:

load $(vStr)

Resident tempXlsPlanChart;

But this one doesn't :

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

temp:

load $(vStr)

Resident tempXlsPlanChart;

I Would like to understand why.

[#5]

Hi Gysbert,

Thanks for the solution.

While the code concats ALL ClmnXls, is there a way to concat only ClmnXls that are currently *selected* the load script? This function is wanted to do conditional loading.

Thanks very much!

Crystal

[#6]

If you want to analyse a portion of the data loaded by the load script you will need to use set analysis, read up on it

as it is complex but well worth while as it’s extremely powerfull …

[#7]

Thanks Clive, I do use set analysis, but the formulas used in load script seems behave differnt from those in the Expression or Dimension. The concat formula works on tabs didn't work in load script.
e.g.

= concat(DISTINCT chr(39)& ClmnXls & chr(39),',')

works in the a text obejct but let vStr = concat(DISTINCT chr(39)& ClmnXls & chr(39),',')  does not work in the load script. vStr become <NULL>.

Do you have any advises of using formula (e.g concat) in load script? Thank you in advance.

[#8]

Hi Crystal,

I thought the concat was a goup aggregation function, can you also use it in let?

(doesn’t it need to be in a load / group by?)

Clive

[#9]

Hi Clive,

I though global should be fine, just like sum() can be used globally, concat can also be used globally?

[#10]

Oh, good.