topic Text Expression in QlikView

Text Expression

Mon, 02 Jan 2012 09:39:04 GMT

Hello everyone,

How I can get the result of a text from an expression in a pivot table? My text is "SUM (A)", which I have saved in a field.

Regards, Daniel

Text Expression

vijay_iitkgp — Mon, 02 Jan 2012 09:55:10 GMT

Hi Daniel,

Where is your text. Is this a column value or a variable ??

Re: Text Expression

Mon, 02 Jan 2012 09:59:44 GMT

Is a column in a table. One of my tables is as follows:

FIELD1 FIELD2

A SUM (R1)

B SUM (R2)

C SUM (R1) + SUM (R2)

Text Expression

Mon, 02 Jan 2012 13:07:08 GMT

Hi Daniel,

if I understand correctly you store your variables in a field of a table that you load into your qvw file.

dgonzalez wrote:
My text is "SUM (A)", which I have saved in a field.

You can:

use the following loop to load your "Formulas" table into variables
for i=0 to noofrows('Formulas')-1
LET var_name= peek('Field1',i,'Formulas');
LET $(var_name) = peek('Field2',i,'Formulas');
TRACE Load Variable: $(var_name) = $($(var_name));
next i
use those variables with Dollar Sign Expansion to have Qlikview convert the expression text into a formula which gets calculated.
eg. your first variable "A" which contains the expression "Sum (R1)" can be used in a chart expression by using the variable "$(A)" which would have the same result as typing "Sum (R1)" into the expression.

If I misunderstood your question then it might be good to attach a sample qvw file so that we can see what you are trying to do.

Ingo.

Re: Text Expression

Mon, 02 Jan 2012 15:29:40 GMT

I can not understand you. I enclose my QlikView to see my situation. I need the column "Result" show me the value.

I hope you can help me. Regards, Daniel

Re: Text Expression

Tue, 03 Jan 2012 09:17:12 GMT

Anyone know how I can solve this problem?

Regards, Daniel

Re: Text Expression

Miguel_Angel_Baeyens — Tue, 03 Jan 2012 10:05:43 GMT

Hi Daniel,

You can try something like

$(=FieldValue('FIELD1', RowNo()))

That will return the evaluation of FIELD1. But I don't see any relation between tables, so it's unlikely to return values other than the sum of all values or null (zero) because dimensions and expression have no links.

Hope that helps.

Miguel

Re: Text Expression

Tue, 03 Jan 2012 10:33:21 GMT

I just tested it and show me the result remains the same. I attached the document if you see something. The expression is indicated in the column "New expression."

Regards, Daniel.

Re: Text Expression

Miguel_Angel_Baeyens — Tue, 03 Jan 2012 10:36:31 GMT

Daniel,

Try instead

=$(=FieldValue('FIELD2', RowNo()))

Because the field with the expressions is FIELD2 not FIELD1

Hope that helps.

Miguel

Re: Text Expression

Tue, 03 Jan 2012 10:42:33 GMT

You are right, I'm wrong but still writing always returns the result of the first row. Below is the document again with the correction.

Regards, Daniel

Re: Text Expression

Tue, 03 Jan 2012 10:55:49 GMT

Maybe

Create a Variable (in my example vFormel), assign

=FIELD2

to that variable.

Pay attentaion that you only select one value of FIELD1 and/or FIELD2

Use as Formula within the Chart $(vFormel) (see attached picture). I created a new record with a formula that would calculate some values.

Best regards and a happy new year

Michael

Re: Text Expression

Tue, 03 Jan 2012 11:22:01 GMT

I do not work properly with this solution because it forces me to filter out values correctly.

Regards, Daniel

Re: Text Expression

Tue, 03 Jan 2012 11:27:15 GMT

Daniel,

a couple more questions to maybe help you with a different solution?

- How many expressions will you store in TABLA1 (FIELD2)?

- Can you predict/control the values in FIELD1? Will they continue REG1, REG2, ..., REG6, REG7, ... without gaps? Could those values in FIELD1 also be called 1,2,3,4,5,6, 7 ...

Ingo.

Re: Text Expression

Tue, 03 Jan 2012 11:34:54 GMT

I can have several different formulas records. With "=$(=FieldValue('FIELD2', RowNo()))" almost works, except that for some reason this always return me the value of the first row. See the attached document.

Regards, Daniel

Text Expression

swuehl — Tue, 03 Jan 2012 11:45:57 GMT

I believe the dollar sign expansion with the equal sign is evaluated in global context in this case, not in the context of your table chart, so rowno() will always return 1.

Text Expression

Tue, 03 Jan 2012 11:52:11 GMT

And as you think you should put it to work around it? I need qye return the line number in which I find to obtain the correct result.

Regards, Daniel

Text Expression

Tue, 03 Jan 2012 12:10:24 GMT

Daniel,

I think swuehl is right and there is a limitation with Dollar-Sign Expansion and RowNo().

You can see that adding this expression to your table would create the formula you need with the correct RowNo: ='FieldValue('&chr(39)&'FIELD2'&chr(39)&', '&rowno()&')'

... but once you wrap the Dollar Sign around the expression the RowNo() does not work anymore.

Here is a similar post that might give you some other inspiration: http://community.qlik.com/message/164748

Re: Text Expression

swuehl — Tue, 03 Jan 2012 12:20:40 GMT

Try attached, using a pick expression variant, so you create one expression for all rows and select by rowno().

Hope this helps,

Stefan

Re: Text Expression

Miguel_Angel_Baeyens — Tue, 03 Jan 2012 14:59:40 GMT

Hi,

Based on that, a much simpler idea would be using "F3" instead of "RowNo()" in the Pick() function and a Concat() using a numeric sort field (F3 in the example by Stefan):

=Concat(FIELD2, ',', F3)

Using a SubField() with the Concat() will work without need of variables, likely. Testing performance on a huge table to see which one renders faster would be a good idea.

Hope that helps.

Miguel

Text Expression

Tue, 03 Jan 2012 15:28:01 GMT

Hi Stefan,

Your attachments are not properly calculated the value of the expression. The result deveria be:

REG1 = 400

REG2 = 320

REG3 = 720

REG4 = 120

REG5 = 840

Regards, Daniel