topic Re: efficient expression with an If and variable in QlikView

efficient expression with an If and variable

helen_pip — Mon, 24 Jul 2017 20:40:17 GMT

Dear Qlikview user

I am currently carrying out an exoercise where I am aiming to improve the performance and efficiency of some of my expressions

On one particular page I have numerous expressions set up as follows:

If($(vShow2WW) = 1,

count({$<

CWT_Analysis_Category = {'CATEGORY1'},

data_source = {' published CWT', 'Somerset'}>}

ID_Number),

If($(vShow2WW) = 0,

count({$<

CWT_Analysis_Category = {'CATEGORY2'},

data_source = {' 31 Day published CWT'}>}

ID_Number),

If($(vShow2WW) = 2,

count({$<

CWT_Analysis_Category = {'CATEGORY3'},

data_source = {'62 Day published CWT'}>}

ID_Number)

)))

I don't think I can use Pick(Match with the variables? I am however thinking of going back to the script and change some of descriptions for Flags

Any help on how to improve performance with this type of expression would be greatly appreciated

Kind Regards

Helen

Re: efficient expression with an If and variable

sunny_talwar — Tue, 25 Jul 2017 00:35:43 GMT

You should be able to use Pick Match... May be this

Pick(RangeSum($(vShow2WW), 1),

count({$<

CWT_Analysis_Category = {'CATEGORY2'},

data_source = {' 31 Day published CWT'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY1'},

data_source = {' published CWT', 'Somerset'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY3'},

data_source = {'62 Day published CWT'}>}

ID_Number)

))

Re: efficient expression with an If and variable

helen_pip — Tue, 25 Jul 2017 08:39:25 GMT

Hi Sunny

Thanks for your help! This works for me!

Thanks

Helen

Re: efficient expression with an If and variable

sunny_talwar — Tue, 25 Jul 2017 11:07:42 GMT

That is great, please close the thread if you got what you wanted.

Best,

Sunny

Re: efficient expression with an If and variable

helen_pip — Tue, 25 Jul 2017 14:40:20 GMT

Hi Sunny

I have marked it as correct and thank you for your help, but am now starting to get a little more ambitious and am trying to use RangeSum() with 2 variables

I.e.

Pick(RangeSum($(vShow2WW),$(v_Selected),1),

count({$<

CWT_Analysis_Category = {'CATEGORY2'},

data_source = {' 31 Day published CWT'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY1'},

data_source = {' published CWT', 'Somerset'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY3'},

data_source = {'62 Day published CWT'}>}

ID_Number)

))

The new variable $(v_Selected) has two options 1 and 2, but the option 2 seems to always get missed out. Have a written the expression accurately to accomodate a second variable?

Any advice is greatly appreciated

Kind Regards

Helen

Re: efficient expression with an If and variable

sunny_talwar — Tue, 25 Jul 2017 14:44:07 GMT

So vShow2WW can equal 0, 1 or 2? and v_Selected can equal 1 or 2? So based on this you have 4 possibilities? Is this right?

0+1 = 1

0+2 = 2

1+1 = 2

1+2 = 3

2+1 = 3

2+2 = 4

May be you need this (Removing , 1 from RangeSum)

Pick(RangeSum($(vShow2WW),$(v_Selected)),

count({$<

CWT_Analysis_Category = {'CATEGORY2'},

data_source = {' 31 Day published CWT'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY1'},

data_source = {' published CWT', 'Somerset'}>}

ID_Number),

count({$<

CWT_Analysis_Category = {'CATEGORY3'},

data_source = {'62 Day published CWT'}>}

ID_Number)

))

Re: efficient expression with an If and variable

helen_pip — Tue, 25 Jul 2017 15:02:45 GMT

Hi Sunny

Yes, you are right, they are the combinations. I have taken off the 1, but cannot get an exact match. I am also playing around with different scenarios

I.e. Pick(RangeSum($(v_Selected),$(vShow2WW),0,1) to see if the range works

Thanks for your suggestion though...I will have a little play about with it!