topic Re: Store excel data into variables in QlikView

Store excel data into variables

tamilarasu — Wed, 13 May 2015 06:39:44 GMT

I want to store the below table into variables while loading the file. Can anyone suggest me a load script (loop!!) for this.

Output should be loaded as variables like below

Thank you.

Re: Store excel data into variables

mohammadkhatimi — Wed, 13 May 2015 07:05:58 GMT

Hie...

What is your exact requirment????

Re: Store excel data into variables

petter — Wed, 13 May 2015 07:07:08 GMT

Should looklike this .... I am typingon my phone so it might not be working 100%...

Input:

LOAD

FROM

Sample.xlsx (....);

FOR row=1 TO NoOfRows('Input')

Target.$(=Peek('Parameter',row-1)) = Peek('Target',row-1);

Weight.$(=Peek('Parameter',row-1)) = Peek('Weight',row-1);

NEXT;

DROP TABLE Input;

Re: Store excel data into variables

tamilarasu — Wed, 13 May 2015 07:14:34 GMT

Hellooo,

It's hard to explain. I need to concatenate the above values with some expressions. I can manage and provide the output by using this method.

Re: Store excel data into variables

Ralf-Narfeldt — Wed, 13 May 2015 07:16:46 GMT

Directory;

Sample:

LOAD Parameter,

Target,

Weight

FROM

Sample.xlsx

(ooxml, embedded labels, header is 1 lines, table is Sheet1);

Variables:

Load 'Target.' & Parameter As Variable,

Target As Value resident Sample;

Load 'Weight.' & Parameter As Variable,

Weight As Value resident Sample;

For vI = 1 to NoOfRows('Variables')

Let vVariable_Name = Peek('Variable',vI,'Value');

Let [$(vVariable_Name)] = Peek('Value',vI,'Value');

Next vI

Re: Store excel data into variables

tamilarasu — Wed, 13 May 2015 07:35:28 GMT

Hello Ralf,

Thanks. I have changed the value of vl to 0 (For vI = 0 to NoOfRows('Variables')) and the code is working as expected.

Re: Store excel data into variables

tamilarasu — Wed, 13 May 2015 07:45:47 GMT

Dear Petter,

I have adopted the code but the parameter names are not fetched correctly. So it's replacing the value again and again. See the below screenshots.

I guess we need to adjust the below part.

Target.$(=Peek('Parameter',row-1))

Weight.$(=Peek('Parameter',row-1))