topic Re: indirect reference to variables in QlikView

indirect reference to variables

Mon, 28 Aug 2017 14:41:08 GMT

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!

Re: indirect reference to variables

felipedl — Mon, 28 Aug 2017 17:31:57 GMT

Hi Namhi,

Do something like this

Mapping

FactorSelect:

Load * inline

[

Id,Value

1,$(vFactor1)

2,$(vFactor2)

3,$(vFactor3)

];

data:

Load

Name,

Re: indirect reference to variables

rubenmarin — Mon, 28 Aug 2017 17:49:09 GMT

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.

Re: indirect reference to variables

Tue, 29 Aug 2017 07:23:34 GMT

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

rubenmarin — Tue, 29 Aug 2017 10:00:27 GMT

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

)

Re: indirect reference to variables

Tue, 29 Aug 2017 12:16:59 GMT

Greate, this works! Thanks a lot for your help