Alternative for if statement also to be written in script while it is used in expression

[Anonymous]

=if(PG='M'

or PG='P others'

or PG='NE'

or PG='PE'

or PG='HC'

or PG='EC'

or PG='FOOD'

or PG='BIO'

or PG='DIS

or PG='SB'

or PG='Ca'

or PG='PH

or PG='PRO'

or PG='SEYO',dual('Others',26),

if(PG='CP',dual('CPS',12),

if(PG='PC',dual('PCS',10),

if(PG='VC',dual('VCS',11),

if(PG='ALL',null(),PG)))))

[tresB]

Check the references given here: Why is using Pick and Match functions combination thought to be faster than nested IF statements?

[sunny_talwar]

May be try this:

=If(Match(PG, 'M', 'P others', 'NE', 'PE', 'HC', 'EC', 'FOOD', 'BIO', 'DIS, 'SB', 'Ca', 'PH', 'PRO', 'SEYO', Dual('Others',26), Pick(Match(PG, 'CP', 'PC', 'VC', 'ALL') + 1, PG, Dual('CPS',12), Dual('PCS', 10), Dual('VCS',11), Null()))

or

If(Match(PG, 'M', 'P others', 'NE', 'PE', 'HC', 'EC', 'FOOD', 'BIO', 'DIS, 'SB', 'Ca', 'PH', 'PRO', 'SEYO', Dual('Others',26), Pick(Match(PG, 'CP', 'PC', 'VC', 'ALL') + 1, PG, Dual('CPS',12), Dual('PCS', 10), Dual('VCS',11), Null())) as NewPG

[Anonymous]

why you use +1

[sunny_talwar]

For when it doesn't meet the criteria, the match will give 0 and then adding 1 to will give the value associated with PG. and then all the values within Match will increment by 1 for pick to do its job.

If(Match(PG, 'M', 'P others', 'NE', 'PE', 'HC', 'EC', 'FOOD', 'BIO', 'DIS, 'SB', 'Ca', 'PH', 'PRO', 'SEYO', Dual('Others',26), Pick(Match(PG, 'CP', 'PC', 'VC', 'ALL') + 1, PG, Dual('CPS',12), Dual('PCS', 10), Dual('VCS',11), Null())) as NewPG