topic Re: Chart Expression stored in excel file in QlikView

Chart Expression stored in excel file

Sun, 04 Jan 2015 01:28:57 GMT

Hi,

I'm dealing wit the following problem:

I want to store chart expression on a excel file to use them on a straight table as follows: (example)

(Excel file)

Column A Column B

A sum(1)

B count(1)

C Avg(1)

then, on qlikview, creating a straight table, with Column A as Dimension, use column B as expression.

How can I do it? I can't make expression work...

Thanks in advance,

Emílio

Re: Chart Expression stored in excel file

ecolomer — Sun, 04 Jan 2015 02:59:50 GMT

See this:

http://community.qlik.com/message/286881#286881

Re: Chart Expression stored in excel file

Sun, 04 Jan 2015 04:46:05 GMT

hi,

just load the excel file and create variables from it, then use if statement to shoot the variables to the expression:

Directory;

mydata:

LOAD RowNo() as ID,

[Column A],

[Column B]

FROM

DATA147291.xlsx

(ooxml, embedded labels, table is Sheet1);

//now let's create the variables holding the expressions

LET myCount = peek('ID',-1,[mydata]);

FOR _i = 0 to $(myCount);

LET _varName = Peek('Column A',$(_i),[mydata]);

LET _varValue = Peek('Column B',$(_i),[mydata]);

//Now create the variables

SET [$(_varName)] = $(_varValue);

NEXT _i;

and then to shoot variables from the expression use: (note: you can use other methods here at expression definition level).

=IF ([Column A] = 'A', $(A),

IF ([Column A] = 'B', $(B),

IF ([Column A] = 'C', $(C)

)

where every variable is created based on the value of column A. for instance if colum A should contain mickey then a variable mickey would exist after script execution.

enclosed the qvw file.

regards,

Re: Chart Expression stored in excel file

Mon, 05 Jan 2015 12:04:39 GMT

Thanks for the help.

Re: Chart Expression stored in excel file

Anonymous — Wed, 14 Jun 2017 15:21:46 GMT

This was very useful, thanks for the example Mario.