IF THEN ELSE STATEMENT IN Calculated Column HANA

[former_member543694]

Hi Experts,

I have a requirement to fill one column with a 3 digit code based on few conditions.

I am trying if then else statement/case in calculated column in graphical view. I searched in the forum but I am not able to identify the right thread which solves my problem.

I have these date columns in my project node - 0POS_DATE , 0PSTNG_DATE, 0DUEDATE

based on the fileds following is the logic I am trying to implement. Please guide me.

CASE

When "0POS_DATE" <> '00000000' Then '231'

When "0PSTNG_DATE" = '00000000' OR TO_DATE ('2018-09-01') < "0PSTNG_DATE" Then '723'

When "0DUEDATE"='00000000' OR TO_DATE ('2018-09-01') <= "0DUEDATE" Then '654'

When DAYS_BETWEEN("0DUEDATE" , TO_DATE (2018-09-01')) > 100 Then '211'

ENDCASE.

[pfefferf]

In a calc. engine calculated column you have to stack several if-function calls to fulfill your requirement. For instance:

if("OPOS_DATE" <> '00000000', '231', 
  if("OPSTNG_DATE" = '00000000' or date('2018-09-01') < date("OPSTNG_DATE"), '723', 
    if("ODUEDATE" = '00000000' or date('2018-09-01') <= date("ODUEDATE"), '654', 
      if(days_between(date("ODUEDATE"), date('2018-09-01')) > 100, '211', '')
    )
  )
)

Regards,
Florian

[former_member543694]

Hi Florain thank you for the reply it helped me a lot.

ONETDUEDATE - NVARCHAR LENGTH 8

I also tried with this expression but no use

if(daysbetween("0NETDUEDATE" ,date( '20180911')) > 100, '211','')

if(daysbetween("0NETDUEDATE" , '20180911') > 100, '211','')

[former_member283461]

You may convert from string to date like TO_DATE('2018-09-11','YYYY-MM-DD') and execute in SQL engine.

[pfefferf]

Sorry, you get the error, because the columns are not in date format. I updated my answer above to convert also the relevant columns to date values.

[former_member543694]

Thank you Florain the solution you have given is working fantastic. One last question please.

Now I am trying to replace the constant date i.e '2018-09-11' with input parameter - IP_DATE.IP_DATE will have the user given date and by default it would be current date.

I have written an expression in it - replace(leftstr(string(now()),10),'-','')

I tried to replace the constant date with IP_DATE ( Data type DATE) in the expression but it is throwing an error...

if(daysbetween(DATE("0DUEDATE"),DATE('$$IP_DATE$$')> 100, '100','')

Then I tried to create an calculated filed CF_DATE and passed the value of IP_DATE to but and then used CF_DATE in expression but now I am not getting any error but I am getting blank values.

if(daysbetween(DATE("0DUEDATE"),DATE("IP_DATE"))> 100, '100','')

[pfefferf]

I assume that your date parameter is already of type Date. Then you do not need to convert it to a date with the "date" function.

if(daysbetween(DATE("0DUEDATE"), '$$IP_DATE$$') > 100, '100','')

[former_member543694]

Hi Florian, I tried this previously but its not working, its throwing an error saying data type format error

[pfefferf]

According to your examples below you did not try that.

Independent of that. I tested it on my system and it works fine.

[former_member543694]

Hi Florian, it is working if I remove single quotes for input parameter and by adding the DATE function.

Below statement is working good.

if(daysbetween(DATE("0DUEDATE"), DATE($$IP_DATE$$)) > 100, '100','')

Thank you for helping me to fix the issues.