Pick Match value list as a variable

rcorcoran — Wed, 23 Dec 2015 10:47:10 GMT

I am trying to use the Pick(Match(Valuelist function. It works fine for me but I will use the valuelist a lot in the document so want to put it in a variable that I only have to change once if wording etc changes. I have tried as follows but it won't work:

original expression that works:

if(isnull(Pick(Match(ValueList('Total no. of persons in receipt of a HCP (Monthly target) ',
'Total no. of persons in receipt of a DDI HCP (Monthly target) ',
'Total no. of new HCP Clients (Annual target) ',
'INTENSIVE HCP number of persons in receipt of an Intensive HCP at a point in time (capacity)',
'Number of new Intensive HCPs, annually '),

'Total no. of persons in receipt of a HCP (Monthly target) ',
'Total no. of persons in receipt of a DDI HCP (Monthly target) ',
'Total no. of new HCP Clients (Annual target) ',
'INTENSIVE HCP number of persons in receipt of an Intensive HCP at a point in time (capacity)',
'Number of new Intensive HCPs, annually '),

num((((Avg({$<[HCP]={2,14},ReferredMM={1}>}ActualValue)))), '##0'),
'',
num((((Avg({$<[HCP]={3,15},ReferredMM={1}>}ActualValue)))), '##0'),
num((((Avg({$<[HCP]={30,36},ReferredMM={1}>}ActualValue)))), '##0'),
'',)),Null(),

Pick(Match(ValueList('Total no. of persons in receipt of a HCP (Monthly target) ',
'Total no. of persons in receipt of a DDI HCP (Monthly target) ',
'Total no. of new HCP Clients (Annual target) ',
'INTENSIVE HCP number of persons in receipt of an Intensive HCP at a point in time (capacity)',
'Number of new Intensive HCPs, annually '),

'Total no. of persons in receipt of a HCP (Monthly target) ',
'Total no. of persons in receipt of a DDI HCP (Monthly target) ',
'Total no. of new HCP Clients (Annual target) ',
'INTENSIVE HCP number of persons in receipt of an Intensive HCP at a point in time (capacity)',
'Number of new Intensive HCPs, annually '),

num((((Sum({$<[HCP]={2,14},ReferredMM={1}>}ActualValue)))), '##0'),
'',
num((((Sum({$<[HCP]={3,15},ReferredMM={1}>}ActualValue)))), '##0'),
num((((Sum({$<[HCP]={30,36},ReferredMM={1}>}ActualValue)))), '##0'),
'',))

New expression:

if(isnull(Pick(Match(ValueList($(vHCPList)),

num((((Avg({$<[HCP]={2,14},ReferredMM={1}>}ActualValue)))), '##0'),
'',
num((((Avg({$<[HCP]={3,15},ReferredMM={1}>}ActualValue)))), '##0'),
num((((Avg({$<[HCP]={30,36},ReferredMM={1}>}ActualValue)))), '##0'),
'',))),Null(),

Pick(Match(ValueList($(vHCPList)),

num((((Sum({$<[HCP]={2,14},ReferredMM={1}>}ActualValue)))), '##0'),
'',
num((((Sum({$<[HCP]={3,15},ReferredMM={1}>}ActualValue)))), '##0'),
num((((Sum({$<[HCP]={30,36},ReferredMM={1}>}ActualValue)))), '##0'),
'',)))

Variable:

='Total no. of persons in receipt of a HCP (Monthly target) ',
'Total no. of persons in receipt of a DDI HCP (Monthly target) ',
'Total no. of new HCP Clients (Annual target) ',
'INTENSIVE HCP number of persons in receipt of an Intensive HCP at a point in time (capacity)',
'Number of new Intensive HCPs, annually '

Re: Pick Match value list as a variable

sunny_talwar — Wed, 23 Dec 2015 10:52:25 GMT

You have specified the variable for ValueList, but not for Match. May be if you add it for Match it might work:

if(isnull(Pick(Match(ValueList($(vHCPList)),$(vHCPList)),

num((((Avg({$<[HCP]={2,14},ReferredMM={1}>}ActualValue)))), '##0'),
'',
num((((Avg({$<[HCP]={3,15},ReferredMM={1}>}ActualValue)))), '##0'),
num((((Avg({$<[HCP]={30,36},ReferredMM={1}>}ActualValue)))), '##0'),
'',)),Null(),

Pick(Match(ValueList($(vHCPList)),$(vHCPList)),

num((((Sum({$<[HCP]={2,14},ReferredMM={1}>}ActualValue)))), '##0'),
'',
num((((Sum({$<[HCP]={3,15},ReferredMM={1}>}ActualValue)))), '##0'),
num((((Sum({$<[HCP]={30,36},ReferredMM={1}>}ActualValue)))), '##0'),
'',))

Re: Pick Match value list as a variable

rcorcoran — Wed, 23 Dec 2015 11:38:16 GMT

Thanks Sunny T that worked and will save me a lot of work.

Re: Pick Match value list as a variable

sunny_talwar — Wed, 23 Dec 2015 11:39:15 GMT

Great, I am glad to hear that

topic Re: Pick Match value list as a variable in QlikView

Pick Match value list as a variable

Re: Pick Match value list as a variable

Re: Pick Match value list as a variable

Re: Pick Match value list as a variable