topic Re: Expression as a variable with $-sign expansion in QlikView

Expression as a variable with $-sign expansion

Wed, 15 Jun 2016 12:27:38 GMT

I have a Pivot Table Chart with four different expressions.

I want to change this chart in a way that it only shows one expression and I can choose the desired calculation (expression) with a button. Therefore I put the expressions in four variables.

Set DefLOGC= Count( { $ <LOGC]={‘J’}, [TACTISCH_BEGIN]=P(Date)> } Distinct ID_ODK );

Set DefOV= Count( { $ <LOGC]={‘N’}, [TACTISCH_BEGIN]=P(Date)> } Distinct ID_ODK );

Set DefTOTAAL= Count( { $ <LOGC]={‘*’}, [TACTISCH_BEGIN]=P(Date)> } Distinct ID_ODK );

Set DefVA= Count( { $ <[VA]={‘J’}> *

<[PARKET_CODE]={94}, [VA_BEGIN]=P(Date)> +

<[PARKET_CODE]={94}, [VA_EIND]=P(Date)> +

<[PARKET_CODE]={40,41,42,43,44,45,46,47,48,49,93},

[VA_BEGIN]={‘<=$(=YearEnd(MakeDate(Max(Year))))’},

[VA_EIND]={‘>=$(=MakeDate(Min(Year)))’}> }

Distinct ID_ODK );

The button stores the name of the definition (DefLOGC, DefOV, DefTOTAAL or DefVA) in a variable vDef. In the chart I use the expression $(=$(vDef)). This works for the variables (expressions) DefLOGC, DefOV and DefTOTAAL, but not for DefVA.

I suppose it has to do with the use of $-sign expansions in the expression, if stored as a variable. It makes no difference if I put double quotes (“”) around the expressions. I have read discussions and blogs on Qlik Community and elsewhere, but can not find a working solution for this problem. I am running out of ideas. Anyone else?

I am using QlikView Version 11.20 SR10 (64-bit)

Re: Expression as a variable with $-sign expansion

sunny_talwar — Wed, 15 Jun 2016 13:42:46 GMT

Can you try this -> $(=vDef)

Re: Expression as a variable with $-sign expansion

sunny_talwar — Wed, 15 Jun 2016 13:44:12 GMT

Alternatively why don't you use a variable to conditionally show hide 4 expression using the conditional expression on the Expression tab?

Re: Expression as a variable with $-sign expansion

Wed, 15 Jun 2016 13:55:57 GMT

If I use $(=vDef) instead of $(=$(vDef)) it is showing the text of the expression and not the calculated result.

Re: Expression as a variable with $-sign expansion

Wed, 15 Jun 2016 14:52:14 GMT

Yes, I can conditionally show/hide the expressions, depending on vDef. However, I don't want to do that for every expression in every chart or table (and there are a lot). Using an if-statement in the expression, works for DefLOGC, DefOV, DefTOTAAL but not for DefAV.

Re: Expression as a variable with $-sign expansion

sunny_talwar — Wed, 15 Jun 2016 15:14:56 GMT

Keeping it $(=vDef) what do you see when you toggle to DefAV?

Re: Expression as a variable with $-sign expansion

Wed, 15 Jun 2016 15:31:28 GMT

Sorry, it should be DefVA, not DefAV. I changed it in the expression and now it works. Next step is to put this expression in a variable. I will try that tomorrow. Thank you so far.

Re: Expression as a variable with $-sign expansion

swuehl — Wed, 15 Jun 2016 15:53:31 GMT

Your issue is caused by QV trying to evaluate the dollar sign expansion in the script, which will not work with your expression. You need to prevent QV from doing this, there are a couple of methods:

- load the variables from a table source

- replace the $ with a dummy character and use LET and replace to replace the characters

- use a concatenated string, spearating $ from ( and thus preventing the DSE

There are a couple of threads that demonstrate all these methods here in the forum (I think the first is also mentioned in the ref manual).

For example, the last method might look like

LET DefVA= 'Count( { $ <[VA]={‘'J'’}> *

<[PARKET_CODE]={94}, [VA_BEGIN]=P(Date)> +

<[PARKET_CODE]={94}, [VA_EIND]=P(Date)> +

<[PARKET_CODE]={40,41,42,43,44,45,46,47,48,49,93},

[VA_BEGIN]={'‘<=$' & '(=YearEnd(MakeDate(Max(Year))))'’},

[VA_EIND]={‘'>=$' & '(=MakeDate(Min(Year)))’'}> }

Distinct ID_ODK )';

Note also that I replaced all existing single quotes with two single quotes to escape the quote.

Re: Expression as a variable with $-sign expansion

Thu, 16 Jun 2016 09:01:42 GMT

With the if-expression stored in a variable "Definitie1" and $(Definitie1) as expression in the chart, it is not working as it should. See also the reaction of swuehl.

Did I understand you wrong?

Re: Expression as a variable with $-sign expansion

swuehl — Thu, 16 Jun 2016 09:14:26 GMT

How do your dollar sign expansions get evaluated in the script within Definitie1 variable?

Re: Expression as a variable with $-sign expansion

Thu, 16 Jun 2016 11:01:25 GMT

Thank you very much for your compact but clear explanation. I have got all three suggestions working in short time. Quite easy when you know how to do it.

Re: Expression as a variable with $-sign expansion

oknotsen — Thu, 16 Jun 2016 11:02:59 GMT

If your question is now answered, please flag the Correct Answer and possible Helpful answers.

If not, please let us know what part of this topic you still like help with .

Re: Expression as a variable with $-sign expansion

Thu, 16 Jun 2016 12:02:49 GMT

If you mean that the evaluation cannot be done properly, then I think you are right. I get an answer, but not the right number. Probably a variable was not refreshed, so it looked like working good. The variable works fine if I change the expression in the way you proposed earlier.

Re: Expression as a variable with $-sign expansion

swuehl — Thu, 16 Jun 2016 13:03:08 GMT

Ok, thanks for the feedback.

I was just wondering how this should have worked.