topic Re: Reference variables from value in field in QlikView

Reference variables from value in field

Fri, 10 Feb 2012 11:53:25 GMT

Hi, I have an issue which should be simple to resolve.... but it isn't!

I'm trying to create a chart that is restricted based on a number of different measurements. Measure A Measure B Measure C etc, that each have a max and min value attributed to them. The chart displays one bar per measure per whether they exceed the max or the min -

Measure a above max

measure a below min

measure b above max

measure b below min

: :

currently I am achieving this by joining the table containing the measures to a max and min input table and restricting the dimensino on the chart to an expression like : =if( Val >= Max then Measure & ' above Max',

if(Val <Min ,Measure & ' below min')

based on a table

Measure Val

A X

B X

joint to an input table

Measure, Max, Min

A, 50, 10

This is so the user can enter their own limits. I am calculating this in the dimension rather than an expression so the user can click on one of the bars and filter for just the minimum or maximum values of A (which then pop up in a separate screen)

I would like the flexibility that you get with variables though, as the input table isn't user friendly. My first option was to create a variable for each min / max and do a nested if, but this was memory intensive.

What I am trying to do now is join the measurements table onto a table which lists variable names in the fields.EG

Measure VariableMaxName VariableMinName

A vAMax vAMin

B vBMax vBMin

: : :

And then reference these in a table. The problem is that I can't get it to call the value from the variable, even in a straight table expression. I've tried =$(VariableMaxName) and then various combinations of quotes, equals signs and even a dollar sign expansion inside a dollar sign expansion!

Could anyone give me insight as to why this isn't working? It just wont return the value of the variable - only a null. I have tried something similar before, but where it references a field, rather than a variable. Help!

Many thanks,

Erica

Re: Reference variables from value in field

Jason_Michaelides — Fri, 10 Feb 2012 15:56:02 GMT

Can you post your app?

Re: Reference variables from value in field

Mon, 13 Feb 2012 10:48:34 GMT

Hi Jason, If I have time I will create a dummy example of what I am trying to do, as the data I am working with is too sensitive to reference in this forum.

I have tried a couple more things, and the question is why can't I reference a variable as part of a dollar sign expansion.

I have now tried referencing the variable as part of the string, and this doesn't work either. Is this something that is

not allowed in Qlikview?

Eg. Where Measures = A and B, variables are vAMax (=1), vAMin (=2), vBMax (=3), vBMin (=4),

Expression Result as displayed in straight table

~~~~~~~~ ~~~~~~

='v'&Measure&'Max' vAMax

=vAMax 1

=$('v'&Measure&'Max') -

=$(='v'&Measure&'Max') -

=v$(Measure)Max -

Although the above work with field names..... please can someone either help, workaround or explain if there is a logical reason as to why this is not working? I can't be the only person to have wanted to reference variables in this dynamic way.

Thanks and Regards,

Erica

Re: Reference variables from value in field

Jason_Michaelides — Mon, 13 Feb 2012 11:14:57 GMT

Sorry Erica, I'm finding it really difficult to follow what you're doing. Can you post some screenshots and exact expressions that you are using if you are not able to post the app?

Re: Reference variables from value in field

Sat, 18 Feb 2012 07:30:57 GMT

Hi Jason, I have had to work around the problem in the end using a pick match statement. Apologies as I didnt have more time, thank you for your interest all the same,

Regards

Erica

Reference variables from value in field

swuehl — Sat, 18 Feb 2012 13:42:35 GMT

Measure is a dimension in your chart?

I believe it's probably a matter of when and in what context your dollar sign expression needs to be evaluated. I believe you would need a syntax that would evaluate the expression in global context, not regarding your dimension value.

A pick / match combination seems to be a reasonably workaround to me.

Regards,

Stefan

Reference variables from value in field

Sat, 18 Feb 2012 16:57:50 GMT

Cheers Stefan. Measure is a dimension in the chart, and the expression shows the counts that are greater / larger.

There are only 5 measures at the moment, so a Pick / match combo is reasonable as you mentioned. eventually I would like to add more, and have some flexibility so if there was a way to reference it that would be smashing! But it works for now

Erica