topic Re: match function in QlikView

match function

Wed, 02 Dec 2015 07:02:40 GMT

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.

Re: match function

tamilarasu — Wed, 02 Dec 2015 07:11:46 GMT

Hi Sowmya,

Use wildmatch

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

Re: match function

rwunderlich — Wed, 02 Dec 2015 07:29:24 GMT

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

Re: match function

Wed, 02 Dec 2015 07:34:03 GMT

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

Re: match function

martinpohl — Wed, 02 Dec 2015 07:42:43 GMT

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

Re: match function

Wed, 02 Dec 2015 07:48:54 GMT

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

Re: match function

Wed, 02 Dec 2015 07:52:40 GMT

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

Re: match function

tamilarasu — Wed, 02 Dec 2015 07:55:34 GMT

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,

Re: match function

Wed, 02 Dec 2015 08:46:13 GMT

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

Re: match function

Thu, 03 Dec 2015 00:59:02 GMT

That's ok and thank you...