topic Use variables in Load expression in QlikView

Use variables in Load expression

Tue, 08 Jul 2014 13:42:48 GMT

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 ?

Re: Use variables in Load expression

alexandros17 — Tue, 08 Jul 2014 13:45:54 GMT

Try with

LOAD $(=vStr)

LOAD $(=$(vStr))

let me know

Re: Use variables in Load expression

Tue, 08 Jul 2014 13:56:02 GMT

Fast replied!

But unfortunately the compiler send Syntax Error message,

This time the variable expansion is null.

Re: Use variables in Load expression

ThornOfCrowns — Tue, 08 Jul 2014 13:59:44 GMT

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

Re: Use variables in Load expression

Tue, 08 Jul 2014 14:06:34 GMT

have you tried LET instead of SET ?

Re: Use variables in Load expression

Gysbert_Wassenaar — Tue, 08 Jul 2014 14:11:34 GMT

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;

Re: Use variables in Load expression

jonathandienst — Tue, 08 Jul 2014 14:13:33 GMT

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

Re: Use variables in Load expression

Wed, 09 Jul 2014 07:34:28 GMT

The Concat function don't create extra comma.

Re: Use variables in Load expression

Wed, 09 Jul 2014 07:57:43 GMT

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

Re: Use variables in Load expression

Wed, 09 Jul 2014 08:08:56 GMT

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

Re: Use variables in Load expression

Wed, 09 Jul 2014 08:09:07 GMT

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

Re: Use variables in Load expression

Wed, 09 Jul 2014 08:46:04 GMT

might be best to use an alias

debug it and publish what the value of the var is

keep trying different stuff

Re: Use variables in Load expression

Wed, 09 Jul 2014 10:14:54 GMT

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.

Re: Use variables in Load expression

Mon, 21 Jul 2014 09:44:32 GMT

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

Re: Use variables in Load expression

Mon, 21 Jul 2014 11:28:40 GMT

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 …

Re: Use variables in Load expression

Tue, 22 Jul 2014 02:06:08 GMT

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.

Re: Use variables in Load expression

Tue, 22 Jul 2014 05:34:03 GMT

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

Re: Use variables in Load expression

Wed, 30 Jul 2014 07:59:42 GMT

Hi Clive,

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

Re: Use variables in Load expression

Wed, 30 Jul 2014 13:45:31 GMT

Oh, good.