topic Re: Variable based on other variable value in QlikView

Variable based on other variable value

Anonymous — Fri, 06 Oct 2017 10:02:58 GMT

Hi All

Is there anyway to define variable based on another variable value?

Let say vVar evaluated value could be 1, 2, 3

Now if I want to use above evaluated value in below definition as I mentioned in bold letters

Is this possible without creating another variable and incorporate in same variable vXYZ?

Because with another variable my report takes tremendous time(never end)

vXYZ=
If(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)) = sum({$<ThExp={'New'}>} ThMin), 1,
If(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)) = sum({$<ThExp={'New'}>} ThLow), 1,
If(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)) = sum({$<ThExp={'New'}>} ThMed), 2,
If(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)) = sum({$<ThExp={'New'}>} ThHgh), 3,
If(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)) = sum({$<ThExp={'New'}>} ThClg), 3

if (vVar = 1, vXYZ * 10,
if (vVar = 2, vXYZ * 20,
if (vVar = 1, vXYZ * 10)))
)))))

I appreciate your suggestion. Thanks a lot

Re: Variable based on other variable value

sunny_talwar — Fri, 06 Oct 2017 11:36:40 GMT

Would it give better performance if you reduce the number of hops... may be... I am not sure and I have never done the testing... but why not consider using Pick(Match()) instead of nested if? May be it can help you here

Re: Variable based on other variable value

pradosh_thakur — Fri, 06 Oct 2017 14:04:26 GMT

please check if any brackets are missed or any syntax error is there. give it a go and see if it works

v_var=

match(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)) ,sum({$<ThExp={'New'}>} ThMin), sum({$<ThExp={'New'}>} ThLow),sum({$<ThExp={'New'}>} ThMed), sum({$<ThExp={'New'}>} ThHgh), sum({$<ThExp={'New'}>} ThClg))

compared to your previous logic(which you can easily adjust)

brown for 1 which will now return 1 and 2

green for 2 which will now return 3 and 4

blue for 3 which will now return 5

pick($(=v_var),v_xyz*10,v*xyz*10,v_xyz*20,v*xyz*20,v_xyz*30)

stalwar1 can correct me if i am wrong anywhere

regards

Pradosh

Re: Variable based on other variable value

Anonymous — Sun, 08 Oct 2017 22:04:04 GMT

Hi Pradosh

In my case I have two variable

1. vVar

2. vXYZ

Your's vVar replacing my vXYZ

//v_var=
match(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)),
sum({$<ThExp={'New'}>} ThMin),
sum({$<ThExp={'New'}>} ThLow),
sum({$<ThExp={'New'}>} ThMed),
sum({$<ThExp={'New'}>} ThHgh),
sum({$<ThExp={'New'}>} ThClg)
)

my vXYZ =

If(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)) = sum({$<ThExp={'New'}>} ThMin), 1,
If(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)) = sum({$<ThExp={'New'}>} ThLow), 1,
If(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)) = sum({$<ThExp={'New'}>} ThMed), 2,
If(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)) = sum({$<ThExp={'New'}>} ThHgh), 3,
If(sum({$<ThExp={'New'}>} RangeMax(ThMin, ThLow, ThMed, ThHgh, ThClg)) = sum({$<ThExp={'New'}>} ThClg), 3
)))))

Is this right?

Pick function needed to use at expression level right?

I think I'm close to the result.

Please advise

Thanks Pradosh and all who suggested best solution.

Thanks

Re: Variable based on other variable value

pradosh_thakur — Mon, 09 Oct 2017 06:11:07 GMT

Hi Satish

yes my first expression should replace your first expression

i thought depending upon the result of first expression you are evaluating the second so suggested the pick function for the second .and yes you can use pick function at the expression level.

regards

Pradosh

Re: Variable based on other variable value

Anonymous — Mon, 09 Oct 2017 19:22:02 GMT

Hi Prabosh

Thanks for your suggestion, It helped

Re: Variable based on other variable value

pradosh_thakur — Tue, 10 Oct 2017 05:19:35 GMT

did you find the solution or still stuck somewhere.

Re: Variable based on other variable value

Anonymous — Tue, 10 Oct 2017 18:02:14 GMT

I found based on your input, Thanks a lot

topic Re: Variable based on other variable value in QlikView

Variable based on other variable value

if (vVar = 1, vXYZ * 10, if (vVar = 2, vXYZ * 20, if (vVar = 1, vXYZ * 10))))))))

Re: Variable based on other variable value

Re: Variable based on other variable value

Re: Variable based on other variable value

Re: Variable based on other variable value

Re: Variable based on other variable value

Re: Variable based on other variable value

Re: Variable based on other variable value

if (vVar = 1, vXYZ * 10,
if (vVar = 2, vXYZ * 20,
if (vVar = 1, vXYZ * 10)))
)))))