How to handle null values using input parameters in Hana calculation view?

[AntonyJerald]

Hello,

There is a table which has null values in a column.

I've created one calculation view, and I'm using input parameters.

It's not displaying data when I execute the Calculation view because of using input parameters, due to null values in the table.

Following is the Filter expression I've used in calculation view:

AND (IN("CUSTOMER_TYPE",'$$ip_customer_type$$') OR MATCH("CUSTOMER_TYPE",'*$$ip_customer_type$$*'))

AND (IN("REGION",'$$ip_region$$') OR MATCH("REGION",'*$$ip_region$$*'))

Region column is null in the table. If I remove the syntax related to region, it gives data.

Plz help me how to make expression for handling null values using input parameters.  Variables doesn't suits to my requirement and hence using input parameters.

Regards,

Antony Jerald.

[Former Member · Accepted solution]

Hi,

It would be helpful if you can specify what input you are trying to pass to the input parameter.

Based on what I understood, as a workaround, you can create a calculated column on REGION field like

IF(ISNULL("REGION"),'',"REGION")

This would convert the null values into a blank sting ('') and then you can do the comparison.

You can also try to use the ISNULL("REGION")  in your filter condition if that helps.

Regards,

Piyush

[AntonyJerald · Accepted solution]

Hi,

But I wanted to fetch data from table based on input values we pass.  If there are null values in the table, how would we fetch it through input parameters in a calculation view?

Regards,

Antony Jerald.

[Former Member · Accepted solution]

Hi Antony,

If that is the case then use the below:

IF(ISNULL("REGION"),'BLANK',"REGION")

And when running the Calculation view pass 'BLANK' keyword as the input to select the Null records. This is based on the assumption that you do not have any valid region named BLANK.

Regards,

Piyush

[AntonyJerald · Accepted solution]

Hi,

Thanks for your reply.

But this impacts the performance.  Is there any other way?

Actually I'm trying to calculate Average for the given combination & overall average using a calculation view.  If I use variables, overall average doesn't gives correct results when we provide values of filters., i.e, the overall count remains the same.  Hence, I opt to use input parameters.

Please suggest.

Regards,

Antony Jerald.

[Former Member]

Hi Antony,

You can apply Filters to the input parameter values through parameter type Derived from Table, however, I think 'is not null' is not supported yet.

Other option would be to write a stored procedures/function that query that table and filters the nulls, and use that SP as function to return the values you need for the input parameter.

What versio nof HANA are you using?

Hope this helps.

Cheers,

Christian.

[AntonyJerald]

Hi,

I'm using Hana studio Version: 2.1.11.

If I use stored procedures, that'll slow down the process because, it would require lot of calculations to be performed for calculating average and overall average.