cancel
Showing results for 
Search instead for 
Did you mean: 

Calculated column and null values

patrickbachmann
Active Contributor
0 Kudos

Hi folks,

I have a fairly simple formula for a calculated column.  I have a field called Flag and it can contain either 'Yes', 'No', or null.  My calculated measure is simply trying to label both 'No' and null results as 'N' like this;

if("Flag" = 'Yes', 'Y','N')

So my interpretation of this formula is that anytime the Flag is set to Yes then place a Y else for every other case (such as 'No' or null) it should just put 'N'.  It is successfully converting Yes to Y and No to N but it leaves nulls as '?' instead of converting them.

Is this a bug?  My Flag field is the result of an outer join with other tables and it's almost as though the calculated column is being calculated BEFORE all of the joins are occurring.  Any suggestions?

Thanks,

-Patrick

Accepted Solutions (1)

Accepted Solutions (1)

former_member184768
Active Contributor
0 Kudos

Hi Patrick,

I always look forward to post from you. They are quite interesting..

Can you please try the following:

if(isnull("Flag"), 'N', if("Flag"='Yes', 'Y', 'N'))

Regards,

Ravi

patrickbachmann
Active Contributor
0 Kudos

Hi Ravi,

Thanks, I'm glad you find my puzzles interesting.    Your solution actually worked for me and I thank you for that and will close this thread soon!  Of course my example, as I typically do, is a dumbed down version of the real problem I am really working on.  I actually have TWO flag fields that I'm combining into a single field with Yes/No as results.  And so it's more complicated but you've led me down the correct path.  Now I've discovered some interesting things about the way (the order) HANA executes the logic in this IF/THEN formula.  I will share it with you once I have it completely working and clear in my own head but I'm very close!

-Patrick

patrickbachmann
Active Contributor
0 Kudos

Ok as promised here are my findings.  First of all I really had two Flag fields derived from two separate joins.  If either of the flag fields contain a Yes then I want my calculated measure to say Yes.  Otherwise all other cases I want a No. 

CODE THAT WORKS

if(isnull("FLAG1") and isnull("FLAG2"), 'N',

if(isnull("FLAG1") and "FLAG2"='Yes', 'Y',

if(isnull("FLAG1") and "FLAG2"='No', 'N',

if(isnull("FLAG2") and "FLAG1"='Yes', 'Y',

if(isnull("FLAG2") and "FLAG1"='No', 'N',

if("FLAG1"='Yes' or "FLAG2"='Yes', 'Y',

'N'))))))

You may think it looks overly complicated having to account for every combination of field values however when i tried to simplify (many various ways I tried) I found that the first time a condition is evaluated that does not include the isNull yet the record is null then it ignore ALL of the remaining ELSE conditions. 

EXAMPLE THAT WONT WORK

if(isnull("FLAG1") and isnull("FLAG2"), 'N',

if("FLAG1"='Yes', 'Y',

if("FLAG2"='Yes', 'Y',

....... ETC

'N'))))))

In this second example if FLAG1 is null HANA does not simply jump to the NEXT condition for FLAG2.  Instead it seems to ignore the rest of the conditions and return NULL regardless of the remaining conditions.

I hope I've explained this so it makes sense.  Another way to explain it; it seems that for an IF condition you would expect this to happen;

IF CONDITION IS TRUE

THEN DO X

OTHERWISE DO Y

It seems if the CONDITION references a NULL value it does not continue to evaluate the rest of the statement (ie: neither the True or False get evaulated), it just returns null.  Seems like a bug to me but will see what others have to say.

Anyway many thanks Ravi for leading me to a solution!

-Patrick

former_member184768
Active Contributor
0 Kudos

, this the exact reason why I like your posts. There is always something more challenging.

Thanks for the in-depth analysis. It is really helpful in understanding how the system behaves.

And now, this is the place where we invite our HANA Super Guru "The Great", I am sure he will have very good and informative explanation on such behavior.

Looking forward to reading his reply on this thread.

Regards,

Ravi

ShashiGarimella
Advisor
Advisor
0 Kudos

Hi Patrick,

Hi i tried to reproduce the issue with a smaller data set but I could not find the issue you mentioned.

My example is as follows.

Table Data

COMPANYPRODUCTQTYPRICE
COMP1PROD13010
COMP1PROD2 40
COMP2PROD31020

Created a CV on top of this table and defined a calculated Attribute "TESTCOLUMN" which has the following expression defined inside.

IF(isnull("QTY") AND isnull("PRICE"),'NULL',IF("PRODUCT" = 'PROD2','SUCCESS','NOT NULL'))

On doing a Data Preview I see my result as follows:

COMPANYPRODUCTTESTCOLUMNQTYPRICE
COMP1PROD2SUCCESS?40
COMP1PROD1NOT NULL3010
COMP2PROD3NOT NULL1020

However, according to your reply above, I should have seen "NULL" instead of "SUCCESS".

Is my understanding of your reply above correct ?

Please let me know if I'm missing something here.

I'm using the very latest Revision build.

Thanks & Regards,

Shashi

lbreddemann
Active Contributor
0 Kudos

Hu Shashi,

spot on!

It's all about NULL comparison here!

NULL values don't compare to any other values, so expressions like equal, smaller/greater than are not decidable for NULLs.

So for Patricks IF statement the result for the NULL values would never be TRUE or FALSE but always NULL.

To catch the NULL values, the ISNULL() function needs to be used instead, just as you demonstrated.

Just have a look into this example:

select flag, case when flag='N'

             then 'YEAH!'

             when flag <>'N'

             then 'YIIIHA!'

              else 'NOPE'

             end out2

from aaa;

FLAGOUT2  
YEAH! 
YEAH! 
NULLNOPE  
NULLNOPE  
YIIIHA!
NULLNOPE  

Easy to see that NULL was neither processed by the equal condition not by the not-equal condition.

Only the ELSE part of the CASE statement worked here.

That can easily be used in the generated column statement:

alter table aaa add (calc_flag varchar(30)

generated always as case when flag='N'

             then 'YEAH!'

             when flag <>'N'

             then 'YIIIHA!'

             when flag is null

             then 'WAS NULL!'

              else flag

             end);

select * from aaa;

COLACOLBFLAGINFO  CALC_FLAG
31  NULL  YEAH!   
41  NULL  YEAH!   
110NULL110    WAS NULL!
NULL'empty'WAS NULL!
'empty'YIIIHA! 
NULL'empty'WAS NULL!

Alright - mystery solved

And stop it already! hehehe

Cheers, Lars

former_member182114
Active Contributor
0 Kudos

Hi Colleagues,

There also another thing to considerer here that is the difference between engines.

Inside SAP Note 1857202  SQL Execution of calculation views, which explain about flag "enforce SQL execution" there's an attachment "CalcViewSQLGenerationPublished.pdf" explaining a bit this difference and null handling. Here's a comment about IF command.

On this .PDF you can found mention to 3, as far as I know undocumented, commands to be used to reduce the difference (jf, sqladd and box).

Try with jf despite if.

Regards, Fernando Da Rós

patrickbachmann
Active Contributor
0 Kudos

Ok thanks guys.  I'm going to spend some more time digesting all your examples but meanwhile I will also mention that my null values are derived not from a single table but as the result of an outer join if it makes any difference. 

ie:

- TABLE1 outer join to Flag1 table

- TABLE1 outer join to Flag2 table

- Calculated column attempting to join the two flags derived from separate tables into a single column at runtime.

So I'm not 100% sure if just having a single table already populated with Flag1 = 'Y' and Flag2 = Null and then running tests like Shashi or Lars would be apples to apples comparison to my example - (I can only guess until I try your examples in a single source table) or will be the same as deriving the results from the joins at query execution time like I'm doing. 

-Patrick

patrickbachmann
Active Contributor
0 Kudos

PS: Also at first glance Fernando's comment seems to be exactly what's happening.  But again I will play some more and update you after.

upamanyu_mukherjee
Participant
0 Kudos

Hi Guys ,

I came across a similar scenario in a calculation view(rev 90), and observed the following things :

Calculated Column : CC_FLAG

--------------------------------------------

if("COL1" = 'A' or "COL2" = 'B' , 'Yes' , 'No')

Since "COL2" has null values as well , the value of CC_FLAG corresponding to those records in null.

So null values are not being handled unless I use coalesce or isnull explicitly.

However , if I apply a filter expression on the calculated column : "CC_FLAG" = 'No' , then in the output I am getting those records where CC_FLAG is 'No' as well as those where CC_FLAG is null.

So , it seems that in this case null values are being handled!

I know that this an old thread but eagerly waiting to hear your thoughts!

Regards,

Upamanyu

patrickbachmann
Active Contributor
0 Kudos

Interesting, maybe something changed in SPS09.  I'm still at rev 82 at the moment.

-Patrick

upamanyu_mukherjee
Participant
0 Kudos

Also it would be really great if you can try it out in 85!!

In 85 ,

Calculated Column : CC_FLAG

--------------------------------------------

if("COL1" = 'A' or "COL2" = 'B' , 'Yes' , 'No')

COL1     COL2     CC_FLAG

--------    ---------    --------------

False     Null          No

Null        False       No

Regards,

Upamanyu

patrickbachmann
Active Contributor
0 Kudos

Ok I have an 85 client too so I will give it a try.

Answers (0)