Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

narender123
Valued Contributor

match function in Add calculated dimension

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

A

B

C

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??

11 Replies
MVP
MVP

Re: match function in Add calculated dimension

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

MVP
MVP

Re: match function in Add calculated dimension

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.

narender123
Valued Contributor

Re: match function in Add calculated dimension

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,

narender123
Valued Contributor

Re: match function in Add calculated dimension

Thanks Jagan .

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

Thanks,

Narender

MVP
MVP

Re: match function in Add calculated dimension

I guess it would be OR instead of AND.

MVP
MVP

Re: match function in Add calculated dimension

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.

narender123
Valued Contributor

Re: match function in Add calculated dimension

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,

narender123
Valued Contributor

Re: match function in Add calculated dimension

Hi Jagan,

Pls see my upper reply.

Thanks,

MVP
MVP

Re: match function in Add calculated dimension

Where is name field?

Community Browser