Skip to main content
Question

Catching dashes (-)

  • May 22, 2020
  • 9 replies
  • 2 views
.
Level 2
Hi all, I have some issues finding null values. The column EISBE in the Marc table returns a numeric value. All values greater than 0 are classified as MTS. Values that are 0 are classified as MTO. However, this column has many null entries (around 70-80%) that I would like to consider as MTO as well. My code: CASE WHEN MARC.EISBE > 0 THEN MTS Material > 0 WHEN MARC.EISBE = 0 THEN MTO Material because 0 WHEN ISNULL(MARC.EISBE) = 1 THEN MTO Material because null WHEN MARC.EISBE IS NULL THEN IS NULL END In the end, the result consists of these values: MTS Material > 0, MTO Material because 0 and -. How can i catch these dashes? Thanks in advance

9 replies

D
Level 1
Forum|alt.badge.img+2
Hi, the CASE WHEN returns NULL when a condition is NULL. Your first condition would therefore evaluate to NULL, and NULL is returned. So the solution would be to check for NULL at the beginning of your CASE WHEN, then the corresponding string will be returned: CASE WHEN ISNULL("MARC"."EISBE") = 1 THEN 'MTO Material because null' WHEN "MARC"."EISBE" > 0 THEN 'MTS Material > 0' WHEN "MARC"."EISBE" = 0 THEN 'MTO Material because 0' END In your query, the last and the second to last conditions are the same, so I left out the last condition here. Best David
.
Level 2
  • .14600770
  • Author
  • Level 2
  • May 26, 2020
Hi David, Thank you for your reply. Unfortunately, your suggestion did not have a better outcome. It didnt change anything Any other ideas?
D
Level 1
Forum|alt.badge.img+2
Do you have other dimensions in your OLAP table? Maybe the NULL values come from missing join partners. You can check that by deactivating all other dimensions of your OLAP table and check if you still have NULL values. If the NULL values come from the missing join partners, you need to introduce the join inside the CASE WHEN. This post might help you: Unable to make NULL Value as the selector in an OLAP Field Let me know if that solves your problem, or if you need further information on this. Best David
.
Level 2
  • .14600770
  • Author
  • Level 2
  • May 27, 2020
Hi David, I have also other dimensions in my table: KNA1.NAME1 VBAK.AUART KNA1.KUNNR VBAP.MATNR VBAP.MATNR_TEXT VBAP.VBELN VBAP.POSNR VBAP.KWMENG VBEP.BMENG VBAP.NETWR_CONVERTED If I deactivate all dimensions, then no null values are displayed. My table contains two rows with these values: MTS Material > 0 and MTO Material because 0
D
Level 1
Forum|alt.badge.img+2
Hi, you would need to introduce the join with the table from your dimensions that is on the n-most side of the join path with MARC. I guess in your case thats the KNA1 table. It should work when you use the new BIND keyword as explained in the link I mentioned above: CASE WHEN ISNULL(BIND("KNA1", "MARC"."EISBE") ) = 1 THEN 'MTO Material because null' WHEN "MARC"."EISBE" > 0 THEN 'MTS Material > 0' WHEN "MARC"."EISBE" = 0 THEN 'MTO Material because 0' END Best David
.
Level 2
  • .14600770
  • Author
  • Level 2
  • May 27, 2020
Hi, Thank you. It works. The missing join partner was VBAP in my case.
A
Level 4

I had a similar problem, the expressions ISNULL()=1 and ISNULL()=0 returned both null values. I solved it using BIND(), it works although I cannot really explain why ISNULL function had such behavior. Does anyone know why? Thanks in advanceimageimageimage

D
Level 1
Forum|alt.badge.img+2

Hi @alejandro.bayon ,

 

the NULL value is not the output of the ISNULL function, but the result of missing join partners from a join with another table.

You can see this when you remove all other Dimensions and KPIs from the OLAP table component - you will not see any NULL values in that column anymore.

In your specific case, it looks like you have 623 cases that don't have a corresponding LFB2 value. The CASE WHEN (and the ISNULL) is executed on the LFB1.AKONT column, producing either "yes" or "no" (no NULL values!). This result is now joined to the Case table (BSEG), because of your Case count KPI, before executing the actual COUNT_TABLE aggregation. And this join produces NULL values for every row of the BSEG table which does not have a join partner with LFB1. So for these rows, there is no "yes" or "no" value that can be joined, and therefore, NULL is returned in those cases.

With BIND you force that join to BSEG earlier: You're not doing it after executing CASE WHEN/ISNULL, but you're doing that join before the CASE WHEN/ISNULL. Now ISNULL can also take the NULL values into account that come from missing join partners.

 

Best

David

A
Level 4

Hi @alejandro.bayon ,

 

the NULL value is not the output of the ISNULL function, but the result of missing join partners from a join with another table.

You can see this when you remove all other Dimensions and KPIs from the OLAP table component - you will not see any NULL values in that column anymore.

In your specific case, it looks like you have 623 cases that don't have a corresponding LFB2 value. The CASE WHEN (and the ISNULL) is executed on the LFB1.AKONT column, producing either "yes" or "no" (no NULL values!). This result is now joined to the Case table (BSEG), because of your Case count KPI, before executing the actual COUNT_TABLE aggregation. And this join produces NULL values for every row of the BSEG table which does not have a join partner with LFB1. So for these rows, there is no "yes" or "no" value that can be joined, and therefore, NULL is returned in those cases.

With BIND you force that join to BSEG earlier: You're not doing it after executing CASE WHEN/ISNULL, but you're doing that join before the CASE WHEN/ISNULL. Now ISNULL can also take the NULL values into account that come from missing join partners.

 

Best

David

Thank you very much

Community Code of ConductAccessibility statement