on 09-25-2013 3:49 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
, 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
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
COMPANY | PRODUCT | QTY | PRICE |
COMP1 | PROD1 | 30 | 10 |
COMP1 | PROD2 | 40 | |
COMP2 | PROD3 | 10 | 20 |
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:
COMPANY | PRODUCT | TESTCOLUMN | QTY | PRICE |
COMP1 | PROD2 | SUCCESS | ? | 40 |
COMP1 | PROD1 | NOT NULL | 30 | 10 |
COMP2 | PROD3 | NOT NULL | 10 | 20 |
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
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;
FLAG | OUT2 |
N | YEAH! |
N | YEAH! |
NULL | NOPE |
NULL | NOPE |
F | YIIIHA! |
NULL | NOPE |
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;
COLA | COLB | FLAG | INFO | CALC_FLAG |
x | 31 | N | NULL | YEAH! |
x | 41 | N | NULL | YEAH! |
A | 110 | NULL | 110 | WAS NULL! |
A | 0 | NULL | 'empty' | WAS NULL! |
C | 5 | F | 'empty' | YIIIHA! |
C | 5 | NULL | 'empty' | WAS NULL! |
Alright - mystery solved
Cheers, Lars
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
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
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
User | Count |
---|---|
88 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.