indirect reference to variables

[#1]

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!

[felipedl]

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.

[rubenmarin]

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.

[#4]

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?

[rubenmarin · Accepted solution]

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))

)

[#6]

Greate, this works!  Thanks a lot for your help