Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
shane_spencer
Specialist
Specialist

Pick WildMatch default value

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

Labels (3)
1 Solution

Accepted Solutions
asinha1991
Creator III
Creator III

 you can try this

 

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

 

View solution in original post

5 Replies
martinpohl
Partner - Master
Partner - Master

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
Specialist
Specialist
Author

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

jaibau1993
Partner - Creator III
Partner - Creator III

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
Creator III
Creator III

 you can try this

 

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

 

shane_spencer
Specialist
Specialist
Author

Thanx. Exactly what I was after.