Payment Term Null/Blank

Hello !
I am trying to compare payment terms (ZTERM) from two tables: LFB1 & EKKO

Currently my formula below is not working because the cases where LFB1.ZTERM is showing as ‘-’ in an OLAP table and EKKO.ZTERM has a value, is not marking it has Different Terms. I have tried ISNULL(column) and = ’ ’ and = NULL.

CASE
WHEN “LFB1”.“ZTERM”= ’ ’ AND “EKKO”.“ZTERM” != ’ ’
THEN ‘Different Terms’
WHEN “LFB1”.“ZTERM” != ’ ’ AND “EKKO”.“ZTERM” = ’ ’
THEN ‘Different Terms’
WHEN “LFB1”.“ZTERM” != “EKKO”.“ZTERM”
THEN ‘Different Terms’
WHEN “LFB1”.“ZTERM” = “EKKO”.“ZTERM”
THEN ‘Matching Terms’
ELSE NULL
END

Hello Neel,

I was able to replicate your Problem. For me ISNULL(“EKKO”.“ZTERM”) always returns a 1 or a 0 and ISNULL(“EKKO”.“ZTERM”) sometimes returned a ‘-’. I think this happens because not every row entry in the EKKO table has a corresponding entry in the “LFB1” table, so the value isn’t missing so much as it was never supposed to exist in the first place. ISNULL checks the “LFB1” Table for missing values, if it has no entry to check it returns a ‘-’. If there is no entry in the EKKO table it returns a ‘-‘ by default there is nothing for it to compare. I tested lots of variations on the case when statement for you. I couldn’t get it to return ‘Different Terms’ for the cases where there is no entry in the LFB1 table. But logically ‘Different Terms’ wouldn’t be the case anyway as its not that the terms don’t match but rather that they are missing from the LFB1 table. Anyway the following case when statement at least correctly identifies the cases where LFB1 should have an entry but its left empty.

CASE

WHEN ISNULL(“LFB1”.“ZTERM”) = 1 AND ISNULL(“EKKO”.“ZTERM”) = 0

THEN ‘Different Terms’

WHEN ISNULL(“LFB1”.“ZTERM”) = 0 AND ISNULL(“EKKO”.“ZTERM”) = 1

THEN ‘Different Terms’

WHEN “LFB1”.“ZTERM” != “EKKO”.“ZTERM”

THEN ‘Different Terms’

WHEN “LFB1”.“ZTERM” = “EKKO”.“ZTERM”

THEN ‘Matching Terms’

ELSE NULL

END

I hope I could a least help you make sense of the situation.

Best wishes,

Calandra