Calculated attribute formula issue

[Former Member]

Hi Team,

I have a issue on creating calculated attribute under calculation view (graphical). My data in the table looks like this,

Column A   Column B     Column C

AB00         0000723400      RD134567

AB00         0000625001      RD345671

CSS          0000623400       CSS

ID00          0000654213      ID0000045

DM00        0000720001       DM000654

and to determine column D, the logic as follows,

if Column A = 'AB00' and Column B = '723400' then 'NA'

       elseif Column A != 'AB00' or Column A ! = 'CSS' or Column A ! = 'ID00' or Column A != 'DM00' then 'NA'

       else Column C

and calculated attribute formula in the view,

if("Column A" != 'AB00' OR "Column A" != 'CSS' OR "Column A" != 'ID00' OR "Column A" != 'DM00','NA',if("Column A" = 'AB00' AND "Column B" = '0000723400','NA',"Column C")) - Result is not correct!

result displayed as

Column A     Column B          Column C        Column D

AB00          0000723400      RD134567      NA

AB00          0000625001      RD345671      NA

CSS           0000623400       CSS              NA

ID00           0000654213      ID0000045      NA

DM00         0000720001       DM000654      NA

Without using OR condition,

if("Column A" != 'AB00','NA',if("Column A" = 'AB00' AND "Column B" = '0000723400','NA',"Column C")) - Result is correct!

result displayed as

Column A   Column B     Column C        Column D

AB00         0000723400      RD134567      NA

AB00         0000625001      RD345671      RD345671

CSS          0000623400       CSS              NA

ID00          0000654213      ID0000045      NA

DM00         0000720001       DM000654     NA

Nested IF condition,

if("Column A" != 'AB00', 'NA', if("Column A" != 'CSS','NA',if("Column A" = 'AB00' AND "Column B" = '0000723400','NA',"Column C"))) - Result is not correct!

result displayed as

Column A     Column B          Column C        Column D

AB00          0000723400      RD134567      NA

AB00          0000625001      RD345671      NA

CSS           0000623400       CSS              NA

ID00           0000654213      ID0000045      NA

DM00         0000720001       DM000654      NA

I would like to know the reason for the failure behind OR and nested if statements. Appreciate your help or input on this issue and I'm using HANA revision 82.

Thanks

Sathish

[former_member210482 · Accepted solution]

Hi Sathish,

As of my understanding all the columns will give NA as result.

if("Column A" != 'AB00' OR "Column A" != 'CSS' OR "Column A" != 'ID00' OR "Column A" != 'DM00','NA',if("Column A" = 'AB00' AND "Column B" = '0000723400','NA',"Column C"))

Your fist condition is "Column A" != 'AB00'. So column d corresponding to CSS ID00 DM00 will be NA. So last three is NA. ie,

CSS           0000623400       CSS              NA

ID00           0000654213      ID0000045       NA

DM00         0000720001       DM000654      NA

Secondly "Column A" != 'CSS'. Here the rest will be set to NA. ie,

AB00          0000723400      RD134567      NA

AB00          0000625001      RD345671      NA

What is the desired output your looking for?

Regards,

Safiyu

[Former Member · Accepted solution]

Hi Safi,

Thanks for your help. I understand.

My condition is, if Column A not belongs to either ABOO or CSS or ID00 or DM00, then it should populate NA, if it belongs to either of them, then it checks the next condition where Column A is AB00 and Column B is 0000723400, if it's true, populate NA, if not, populate whatever there in Column C.

Expected result of column D is

Column A      Column B        Column C     Column D

AB00          0000723400      RD134567       NA

AB00          0000625001      RD345671       RD345671

CSS           0000623400      CSS               CSS

ID00           0000654213      ID0000045       ID0000045

DM00         0000720001      DM000654       DM000654

XYZ           0000720001      XYZ000786      NA

Thanks

Sathish

[former_member210482 · Accepted solution]

Hi Sathish,

That's because you are declaring col D as NA as soon as the first condition is satisfied. Try this

if("Column A" = 'AB00' OR "Column A" = 'CSS' OR "Column A" = 'ID00' OR "Column A" = 'DM00',if("Column A" = 'AB00' AND "Column B" = '0000723400','NA',"Column C"),'NA')

Regards,

Safiyu

[Former Member · Accepted solution]

Thanks Safi, it's working now. I have another question, is it possible to use the same condition using SELECT CASE?

I tried using following query, it's not giving the desired output.

select col A, col B, col c,

case when col A != 'AB00' or Col A != 'CSS' then 'NA'

when  Col B = '0000614002' AND Col A = 'AB00' then 'NA'

else Col C

END as Col D from table 1

Thanks

Sathish

[former_member210482 · Accepted solution]

Hi Sathish,

Yes you can. try the below one

select "COLA", "COLB", "COLC",

case

when "COLA" = 'AB00' or "COLA" = 'CSS' or "COLA" = 'ID00' OR "COLA" = 'DM00' then

case

when  "COLB" = '0000625001' AND "COLA" = 'AB00' then 'NA'

else "COLC"

END

else 'NA'

END as "COLD" from table 1;

This is the exact replication of the calculated column which i provided. Hope this helps.

Cheers,

Safiyu

[Former Member · Accepted solution]

Okay thanks Safi, I will try and let you know.

[former_member210482 · Accepted solution]

Yeah. If you still face issues let me know.

Cheers

Safiyu