topic Re: Convert a field value to an expression in QlikView

Convert a field value to an expression

Tue, 19 Jan 2016 08:42:17 GMT

If you are using a synthetic dimension from a data island is it also possible to store the expressions in this table and use them?

I know there is a pick/match method but this requires to much maintenance for what I need. So in a nutshell I want to use the value stored in the "SynthExpr1" field as the expression for the associated Synthetic dimension. Example attached.

Is it possible?

Re: Convert a field value to an expression

ni_avoss — Tue, 19 Jan 2016 12:01:59 GMT

am I right, that you want to select the expression in field SynthDim1 ?

then you could try the following:

create variables which are holding the expression (in general good for debubbing - no reload necessary if you want to change them) - but with out any "=" at the beginning, so only the text
in SynthExpr1, store the according variables
use this expression: =$(=$(=SynthExpr1))

hope that does it

best,

alex

Re: Convert a field value to an expression

marcus_sommer — Tue, 19 Jan 2016 12:35:12 GMT

Easier as pick(match()) isn't possible - the creation of the pick-match expression might be automated in some way. see here a similar discussion: Re: AllowMacroFunctionsInExpressions AND ActiveDocument.Evaluate()

- Marcus

Re: Convert a field value to an expression

marcus_sommer — Tue, 19 Jan 2016 15:18:57 GMT

This won't work because with a $-sign expansion creates an adhoc-variable which will be evaluated once before the chart is calculated and then applied for each row.

- Marcus

Re: Convert a field value to an expression

jonas_rezende — Tue, 19 Jan 2016 16:30:51 GMT

Hi, Felix180807.

It's possible store expression in field of island table, the variable is previously created. Use Ctrl+Alt+V

Follow the steps below:

Create a variable containing the desired aggregation without the equal sign (=);
In island table insert variable name without $-sign;
In chart table, call the expression $($(=SynthExpr1)).

I attached an simple example for you have idea of how work.

Hope this helps!

Re: Convert a field value to an expression

rwunderlich — Wed, 20 Jan 2016 02:03:24 GMT

I think you can do it as long as only one SynthExpr1 is possible (or you chose one from among alternatives). See attached example.

=$(=SynthExpr1)

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Convert a field value to an expression

Wed, 20 Jan 2016 08:31:31 GMT

Some great examples here and the =$(=SynthExpr1) trick will certainly be handy in the future.

The issue specific to what I'm trying to achieve is that I need the table to be able to show all the various expressions at once - which isn't available via dollar expansion.

I'm going to go with the pick match expression generator.

In case it helps anyone the script looks a bit like this:

LET NumRows=NoOfRows('SYNTHDIMTABLE');
FOR i=0 to $(NumRows)-1

LET SYNTH_MATCH = '$(SYNTH_MATCH)' & ',' & Peek('IDENTITYFIELD',$(i));
LET SYNTH_SALES_EXPR = '$(SYNTH_SALES_EXPR)' & ',' & IF(LEN(Peek('FIELDNAME',$(i)))>0,Peek('FIELDNAME',$(i),'') & CHR(10);

NEXT;

LET SYNTH_SALES_FINAL = 'PICK(MATCH(IDENTITYFIELD$(SYNTH_MATCH))' & CHR(10) & '$(SYNTH_SALES_EXPR)' & ')';

Thanks to everyone for the helpful and insightful responses