Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
)
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.
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.
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?
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))
)
Greate, this works! Thanks a lot for your help