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
Page 1 / 1
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:
In your query, the last and the second to last conditions are the same, so I left out the last condition here.
Best
David
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
Hi David,
Thank you for your reply. Unfortunately, your suggestion did not have a better outcome. It didnt change anything Any other ideas?
Thank you for your reply. Unfortunately, your suggestion did not have a better outcome. It didnt change anything Any other ideas?
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
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
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
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
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:
Best
David
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
Hi,
Thank you. It works. The missing join partner was VBAP in my case.
Thank you. It works. The missing join partner was VBAP in my case.
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 advance
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
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
Reply
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.