Pick Match value list as a variable

[rcorcoran]

Hi

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 '

[sunny_talwar · Accepted solution]

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

[rcorcoran]

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

[sunny_talwar]

Great, I am glad to hear that