Skip to main content
Question

Need help in building KPI

G
Level 3
Forum|alt.badge.img+7

Hi,

 

I need help in building a KPI using two columns, I'm trying to build the KPI but the code is not getting executed for all the rows i.e. the 2nd condition code is not getting excecuted i'm getting the o/p as Blank .

 

Requirement:

 

image 

Below is my code:

 

CASE WHEN ("CONTROLLER_xlsx_Sheet1"."KEY") = '10' AND COALESCE ("CONTROLLER_xlsx_Sheet1"."CONTR", 'RANDOM_VALUE') != 'A50' OR

("CONTROLLER_xlsx_Sheet1"."KEY") IS NULL AND ("CONTROLLER_xlsx_Sheet1"."CONTR") NOT IN ('A01', 'A02', 'A03') OR

(ISNULL ("CONTROLLER_xlsx_Sheet1"."CONTR") = 1 )THEN 'CLEANSE' ELSE 'PERFECT' END

 

image 

 

janpeter.van.d
Level 8
Forum|alt.badge.img+19

Hi @Gundappa Pujari,

 

I think you can improve your code in two ways:

  1. Use brackets to show AND/OR groups
  2. You use two ways for dealing with null values (see marked places), use here the ISNULL() operator

 

CASE

WHEN (("CONTROLLER_xlsx_Sheet1"."KEY") = '10' AND COALESCE("CONTROLLER_xlsx_Sheet1"."CONTR", 'RANDOM_VALUE') != 'A50')

OR (ISNULL ("CONTROLLER_xlsx_Sheet1"."KEY") = 1 AND "CONTROLLER_xlsx_Sheet1"."CONTR" NOT IN ('A01', 'A02', 'A03'))

OR (ISNULL ("CONTROLLER_xlsx_Sheet1"."CONTR") = 1)

THEN 'CLEANSE'

ELSE 'PERFECT'

END

 

I hope this helps.

 

Kind regards,

Jan-peter

G
Level 3
Forum|alt.badge.img+7
janpeter.van.d wrote:

Hi @Gundappa Pujari,

 

I think you can improve your code in two ways:

  1. Use brackets to show AND/OR groups
  2. You use two ways for dealing with null values (see marked places), use here the ISNULL() operator

 

CASE

WHEN (("CONTROLLER_xlsx_Sheet1"."KEY") = '10' AND COALESCE("CONTROLLER_xlsx_Sheet1"."CONTR", 'RANDOM_VALUE') != 'A50')

OR (ISNULL ("CONTROLLER_xlsx_Sheet1"."KEY") = 1 AND "CONTROLLER_xlsx_Sheet1"."CONTR" NOT IN ('A01', 'A02', 'A03'))

OR (ISNULL ("CONTROLLER_xlsx_Sheet1"."CONTR") = 1)

THEN 'CLEANSE'

ELSE 'PERFECT'

END

 

I hope this helps.

 

Kind regards,

Jan-peter

Hello @janpeter.van.d ,

 

Thank you for the suggestion. I tried adding the brackets but still 2nd line code is throwing blank output.

 

image.png 

image 

Best Regards,

Gundappa

D
Level 3

Hi @Gundappa Pujari ,

I think the problem is the first check. Try the following:

CASE WHEN COALESCE("CONTROLLER_xlsx_Sheet1"."KEY", '-') = '10' AND COALESCE ("CONTROLLER_xlsx_Sheet1"."CONTR", 'RANDOM_VALUE') != 'A50' OR

("CONTROLLER_xlsx_Sheet1"."KEY") IS NULL AND ("CONTROLLER_xlsx_Sheet1"."CONTR") NOT IN ('A01', 'A02', 'A03') OR

(ISNULL ("CONTROLLER_xlsx_Sheet1"."CONTR") = 1 )THEN 'CLEANSE' ELSE 'PERFECT' END

G
Level 3
Forum|alt.badge.img+7
daniel.diers12 wrote:

Hi @Gundappa Pujari ,

I think the problem is the first check. Try the following:

CASE WHEN COALESCE("CONTROLLER_xlsx_Sheet1"."KEY", '-') = '10' AND COALESCE ("CONTROLLER_xlsx_Sheet1"."CONTR", 'RANDOM_VALUE') != 'A50' OR

("CONTROLLER_xlsx_Sheet1"."KEY") IS NULL AND ("CONTROLLER_xlsx_Sheet1"."CONTR") NOT IN ('A01', 'A02', 'A03') OR

(ISNULL ("CONTROLLER_xlsx_Sheet1"."CONTR") = 1 )THEN 'CLEANSE' ELSE 'PERFECT' END

THANK YOU @Daniel Diers  for the help 😊

joos.buijs
Level 6
Forum|alt.badge.img+2
gundappa.pujar12 wrote:

THANK YOU @Daniel Diers  for the help 😊

Did this solve the issue @Gundappa Pujari ?

G
Level 3
Forum|alt.badge.img+7
gundappa.pujar12 wrote:

THANK YOU @Daniel Diers  for the help 😊

Hello Joos,

 

Yes, the above code solved my issue :)

Reply


Most helpful members this week

Community Code of Conduct