Pick WildMatch default value

[shane_spencer]

I've got a Currency field that has values like, USD, EURO and UK.

I want to translate UK and EURO to GBP and EUR respectively.

I can do this quite easily in an If formula because there are just two values that need translating, the rest should stay the same. i.e.

If([Currency]='EURO','EUR',If([Currency]='UK','GBP',[Currency])

However in the future there may be currencies that need translating and the nested If statement could get quite large. I'm wondering if it's easy to set a default value using Pick and WildMatch (or Match). The following would translate UK and EURO for me but I would lose USD etc. 

Pick(WildMatch([Currency],'UK','EURO'),'GBP','EURO')

[martinpohl]

use a mapping table

Currency_Map:

mapping load inline [

Cur1, Cur2

EURO, EUR

UK, GBP

];

load

your data,

applymap('Currency_Map',Cur_field,Cur_field) as NewCurrency

from your datasource;

the mapping table can be expanded for many rows you need

regards

[shane_spencer]

I don't want to use an ApplyMap. It's difficult to explain but I need to try to do a simple extract.

[jaibau1993]

Hi!

Just check if match so you can add an else statement:

if(  WildMatch([Currency],'UK','EURO')

          Pick(WildMatch([Currency],'UK','EURO'),'GBP','EURO'),

          [Currency]

)

Regards,

Jaime.

[asinha1991 · Accepted solution]

 you can try this

Pick(WildMatch([Currency],'UK','EURO')+1,[Currency],'GBP','EURO')

[shane_spencer]

Thanx. Exactly what I was after.