topic Re: match function in Add calculated dimension in QlikView

match function in Add calculated dimension

narender123 — Wed, 14 May 2014 05:52:22 GMT

Hi All,

I am using match function in straight table(in add calculated dimention).But my match function is not properly working as per my condition.

Straight table dimention>add calculated dimetion

=if(CPC_CODE='C473','XYZ',

if(CPC_CODE='C430','pqr',

if(match(CPC_CODE,'C4241'),'A',

if(match(CPC_CODE,'C423'),'B',

if(match(CPC_CODE,'C4301','C4302'),'C',

if(match(CPC_CODE,'C4301','C473','C4302','C430','C473'),'D'

))))))

Result is showing

XYZ

pqr

but not showing D.I want to show all condition.

Because in match function for D : CPC_CODE =4310 and 4302 is repeating as we have used CPC_CODE =4310 and 4302 in match function for C.So Match function is not working where CPC_CODE already used in other case.Pls tell what i have to do.Or there is any other function to solve this??

Re: match function in Add calculated dimension

tresB — Wed, 14 May 2014 06:08:38 GMT

Try:

=Pick(Match(CPC_CODE, 'C473', 'C430','C4241','C423' ,'C4301' ,'C4302', 'C4301','C473','C4302','C430','C473'), 'XYZ','pqr', 'A','B', 'C', 'C','D','D','D','D','D')

Re: match function in Add calculated dimension

jagan — Wed, 14 May 2014 06:22:25 GMT

Hi,

Create an inline table like below and use Value as Dimension instead of Calculated dimension.

Data:

LOAD * INLINE [

CPC_CODE, Value

C473, XYZ

C430, pqr

C4241,A

C423, B

C4301, C

C4302, C

C4301, D

C473, D

C4302, D

C430, D ];

Regards,

Jagan.

Re: match function in Add calculated dimension

narender123 — Wed, 14 May 2014 06:26:04 GMT

Hi Tresesco.

Thanks for your reply.

Its not my requirement.

Actually my condition is :

=if(CPC_CODE='C473','XYZ',

if(CPC_CODE='C430','pqr',

if(CPC_CODE='C4241','A',

if(CPC_CODE='C423','B',

if(CPC_CODE='C4301' and CPC_CODE='C4302','C',

if(CPC_CODE='C4301' and CPC_CODE='C473' and CPC_CODE='C4302' and CPC_CODE='C430' and CPC_CODE='C473'),'D'))))))

Here 'if' condition is not working with 'and' condition.

So i have used match function.Match function is working but not for repetition as told in my previous question.

Pls tell me ,is there any other function or how to sort out this??/

Thanks,

Re: match function in Add calculated dimension

narender123 — Wed, 14 May 2014 06:27:28 GMT

Thanks Jagan .

But its not my requirement .Pls see my previous reply.

Thanks,

Narender

Re: match function in Add calculated dimension

tresB — Wed, 14 May 2014 06:29:14 GMT

I guess it would be OR instead of AND.

Re: match function in Add calculated dimension

jagan — Wed, 14 May 2014 06:33:24 GMT

Hi,

I think it should be OR instead of AND, anyway you wont get C and D for C4302 and C4301. You have to create a mapping table.

For C4301 you will get C, and you won't get D, because C is the value you get first and then the if() is terminated.

Regards,

Jagan.

Re: match function in Add calculated dimension

narender123 — Wed, 14 May 2014 06:51:34 GMT

Hi Tresco and Jagan,

Could you tell me how to achieve this with 'and' condition?

because i have cpc_code like

cpc_code value

4301 10

4302 20

4303 30

4304 40

So i have to group them in different pair with 'and' condition.

for cpc_code 4301 and 4302 :- i want name = A , value =30

for cpc_code 4303 and 4304 :- i want name = B , value =70

for cpc_code 4301 - i want name = C , value =10

This is my target.Pls advice.

Thanks,

Re: match function in Add calculated dimension

narender123 — Wed, 14 May 2014 06:52:28 GMT

Hi Jagan,

Pls see my upper reply.

Thanks,

Re: match function in Add calculated dimension

tresB — Wed, 14 May 2014 06:56:20 GMT

Where is name field?

Re: match function in Add calculated dimension

narender123 — Wed, 14 May 2014 07:46:31 GMT

I am making a group of cpc_code, so with these group condition i will produce a name.

if(cpc_code 4301 and 4302 - i want

cpc group value

A 30

Re: match function in Add calculated dimension

tresB — Wed, 14 May 2014 07:54:23 GMT

Now I notice, you want single member to be part of seperate groupings. For this you have to work in the script; kind of generic association for some members. Try to upload your sample app with sample data, would be easier to help you.