match function

[#1]

Hi Friends,

i have used match function to create different field using the existed field,and it is working fine. below is the code.

if(match(Currency,'SGD','USD','EUR','AUD','GBP','HKD','CNH','INR','IN1','TWD','TW1','CN1','CNY'),Currency) as Currency

now  what i want is... i want to merge INR,IN1 as INR and TWD,TW1 as TWD and 'CN1','CNY' as CNY like that and it should come in the same feild. i want the output like this

Currency 1                                           

AUD

CNH

CNY

EUR

GBP

HKD

INR

Others

SGD

TWD

USD

  i have written the below code , but it is not working...

if(match(Currency,'SGD','USD','EUR','AUD','GBP','HKD','CNH',if(match(Currency,'INR','IN1'),'INR'),if(match(Currency,'TWD','TW1'),'TWD') ,

if(match(Currency,'CN1','CNY'),'CNY')),Currency,'Others') as Currency1

can any one help me to do this.

[tamilarasu]

Hi Sowmya,

Use wildmatch

if(WildMatch(Currency,'SGD','USD','EUR','AUD','GBP','HKD','CNH','IN*','TW*','CN*'),Currency) as Currency

[rwunderlich]

I'm not sure why your code is not working. Could you elaborate on what you mean by "not working"? What result are you getting?

-Rob

[#4]

Hi Tamil,

Thank you for your quick reply,

i am having a feild  Source Currency , using match function i divided the data  which is showing in currency feild, i have taken what i want and remaining currency i considered as Others.

but my final output should be 'Currency1', to get that we have to merge some fields in currency

Source Currency
AED
AU1
AUD
BHD	Currency	Currency1
CAD	AUD	AUD
CHF	CN1	CNH
CN1	CNH	CNY=CN1 and CNY
CNH	CNY	EUR
CNY	EUR	GBP
DKK	GBP	HKD
EUR	HKD	INR =IN1 and INR
GBP	IN1	Others
HKD	INR	SGD
ID1	SGD	TWD=TW1 and TWD
ID2	TW1	USD
IDR	TWD
IN1	USD
INR	Others
JPY
SGD
TW1
TWD
US1
USD

[martinpohl · Accepted solution]

change it to this

if(match(Currency,'SGD','USD','EUR','AUD','GBP','HKD','CNH'),Currency,

if(match(Currency,'INR','IN1'),'INR',

if(match(Currency,'TWD','TW1'),'TWD' ,

if(match(Currency,'CN1','CNY'),'CNY','Others')))) as Currency1,

Regards

[#6]

Hi Rob,

i am getting the output like below(Currency). TWD,CNY data is not coming... i want to get the Currency1

Currency	Currency1
AUD	AUD
CNH	CNH
EUR	CNY
GBP	EUR
HKD	GBP
INR	HKD
Others	INR
SGD	Others
USD	SGD
	TWD
	USD

[#7]

Thank you so much Martin.. it is working..

[tamilarasu]

I misunderstood your question. You can follow Martin solution,

Or somrthing like below,

if(match(Currency,'SGD','USD','EUR','AUD','GBP','HKD','CNH'),Currency,

if(Wildmatch(Currency,'IN*'),'INR',

if(Wildmatch(Currency,'TW*'),'TWD' ,

if(Wildmatch(Currency,'CN*'),'CNY','Others')))) as Currency,

[#9]

hi,

Below expression will solve your purpose:

pick(match(A,'SGD','USD','EUR','AUD','GBP','HKD','CNH','INR','IN1','TWD','TW1','CNY','CN1'),'SGD','USD','EUR','AUD','GBP','HKD','CNH','INR','INR','TWD','TWD','CNY','CNY')

Regards,

Devanand

[#10]

That's ok and thank you...