Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Highlighted
Not applicable

indirect reference to variables

Hello QV experts,

I'm trying to reference variables using field values. I want to set variables as below, using Input Box, as below, and reference them using field values in the existing data.

vFactor1 = 1.2

vFactor2 = 1.3

vFactor3 = 2.4

So the data sample as below, I would like to set the "FactorSelect" based on the value in the Category field, "vFactor" & Category, and use it for further calulation in chart objects.

Name    Category   FactorSelect

a             1              1.2

b             1              1.2

c             2               1.3

d             3               2.4

I tried using $('vFactor' & [Category]) but it doesn't return any value. Is it possible to indirectly refer to variables and how can I do this?

Thanks in advance!

1 Solution

Accepted Solutions

Re: indirect reference to variables

Hi Namhi, just to comment that Felip code is using the variables to assign field values, but will need a reload of the data to reflect changes in variable values.

If you need to reflect changes without a reload and the values should change based on category then you have to check Category outside outside the $-expansion, ie:

=Sum([Price] *

   Pick(Category, $(vFactor1), $(vFactor2), $(vFactor3))

)

5 Replies
felipedl
Valued Contributor III

Re: indirect reference to variables

Hi Namhi,

Do something like this

Mapping

FactorSelect:

Load * inline

[

     Id,Value

     1,$(vFactor1)

     2,$(vFactor2)

     3,$(vFactor3)

];

data:

Load

     Name,

     Category

     ApplyMap('FactorSelect',Category) as FactorSelect

From [whatever];

Felipe.

Re: indirect reference to variables

Hi Namhi, $-expansion is done before the table it's calculated, so it won't use the value of each of row.

It can be used with a selector that ensures that only one category is selected, but for a row by row calculations you can load as a field value as noted above by Felip.

Not applicable

Re: indirect reference to variables

Thanks a lot. I guess I wasn't very clear. I'm trying to do this in sheet objects instead in script. What I want to do is set

factors, vFactor1-3, using Input objects, and assign them to loaded data in sheet objects for further calculation.

Name    Category   Price  FactorSelect

a             1               120            1.2

b             1               110             1.2

c             2               200             1.3

d             3               130              2.4

What I'm trying to do is effectively, below

= sum ( [Price] *  [FactorSelect] )

but because this FactorSelect field doesn't exist and should be set dynamically (after script has run by the user inupt)

I am attempting to indirectly reference vFactor1-3 depending on the value in the Category field.

= sum ( [Price] *  ['vFactor' & $(Category)] )

Is possible to do in QlikView?

Re: indirect reference to variables

Hi Namhi, just to comment that Felip code is using the variables to assign field values, but will need a reload of the data to reflect changes in variable values.

If you need to reflect changes without a reload and the values should change based on category then you have to check Category outside outside the $-expansion, ie:

=Sum([Price] *

   Pick(Category, $(vFactor1), $(vFactor2), $(vFactor3))

)

Not applicable

Re: indirect reference to variables

Greate, this works!  Thanks a lot for your help