Skip to main content
Solved

Whats the best way to handle NULL results in PU-Functions when 0 is needed?

  • July 25, 2024
  • 4 replies
  • 36 views
thomas.keim
Level 10
Forum|alt.badge.img+3

Hello Celopeers,

 

whats the best way to handle NULL results in PU-Functions when I need 0 if no PU results are available?

 

Here a example how I do it today:

 

CASE WHEN ISNULL(PU_SUM(VBAK, VBAP.KWMENG)) = 1

 

THEN 0

 

ELSE PU_SUM(VBAK, VBAP.KWMENG)

 

END

 

Is there a way to make it better?

 

BR, Thomas

 

 

 

Best answer by janpeter.van.d

Hi @thomas.keim, 

 

Computationally wise, COALESCE would be faster I think, but sometimes harder to read for non-technical users. COALESCE returns the first not-null statement, drastically reducing your code to:

 

COALESCE(PU_SUM(VBAK, VBAP.KWMENG), 0)

 

I.e.: if your sum is NULL, it will go to the second argument, being '0'.

 

I hope this helps!

4 replies

K
Level 3
  • kayla.ledbe
  • Level 3
  • July 25, 2024

That is how I would code it. **Following this post in case someone else recommends a better way. 😊

matt.witty13
Level 10

Guess it depends on what you're trying to accomplish. Just looking at your formula, I don't think you need to do a pull-up but just a SUM(VBAP.KWMENG) in the event the table/chart/etc. is looking at Sales Orders.

 

If you do need a Pullup, you could do PU_SUM("VBAK","VBAP"."KWMENG", VBAP.KWMENG IS NOT NULL) to see all non-nulls or do PU_COUNT("VBAK","VBAP"."KWMENG", VBAP.KWMENG IS NULL) should work.

 

If you're just trying to see records where VBAP.KWMENG doesn't exist,

COUNT(CASE WHEN VBAP.KWMENG is NULL THEN 1.0 ELSE 0.0 END)

 

Hope these help!

manuel.wetze
Level 9
Forum|alt.badge.img+8

You could check out the REMAP_INTS function: https://docs.celonis.com/en/remap_ints.html

maybe that works. Unsure if it caters your default value (ELSE-CASE) correctly though.

 

Should besomethin like: REMAP_INTS((PU_SUM(VBAK, VBAP.KWMENG)), [NULL,0], PU_SUM(VBAK, VBAP.KWMENG)) but I haven't tried myself yet.

 

In general I try to use the REMAP functions over CASE WHEN whenever possible as its clearer to read and therefore easier to maintain.

 

janpeter.van.d
Level 12
Forum|alt.badge.img+26

Hi @thomas.keim, 

 

Computationally wise, COALESCE would be faster I think, but sometimes harder to read for non-technical users. COALESCE returns the first not-null statement, drastically reducing your code to:

 

COALESCE(PU_SUM(VBAK, VBAP.KWMENG), 0)

 

I.e.: if your sum is NULL, it will go to the second argument, being '0'.

 

I hope this helps!

Community Code of ConductAccessibility statement