Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
narender123
Specialist
Specialist

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
tresesco
MVP
MVP

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

jagan
Luminary Alumni
Luminary Alumni

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

Thanks Jagan .

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

Thanks,

Narender

tresesco
MVP
MVP

I guess it would be OR instead of AND.

jagan
Luminary Alumni
Luminary Alumni

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
Specialist
Specialist
Author

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
Specialist
Specialist
Author

Hi Jagan,

Pls see my upper reply.

Thanks,

tresesco
MVP
MVP

Where is name field?