topic Re: Pick WildMatch default value in QlikView

Pick WildMatch default value

shane_spencer — Sat, 16 Nov 2024 02:13:06 GMT

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

Re: Pick WildMatch default value

martinpohl — Wed, 16 Oct 2019 07:40:40 GMT

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

Re: Pick WildMatch default value

shane_spencer — Wed, 16 Oct 2019 07:43:04 GMT

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

Re: Pick WildMatch default value

jaibau1993 — Wed, 16 Oct 2019 08:12:48 GMT

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.

Re: Pick WildMatch default value

asinha1991 — Wed, 16 Oct 2019 08:35:04 GMT

you can try this

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

Re: Pick WildMatch default value

shane_spencer — Wed, 16 Oct 2019 12:52:03 GMT

Thanx. Exactly what I was after.