Special cases for minimum()

[former_member184624]

Dear HANA Experts.

Please find the below sample data and attached my model also.

Scenario 1 : I want to calculate the minimum value where the depth value is less than or equal to 0 specific to SRPNO, then Minimum is 0. (SRPNO = 2001). If all records are less than 0 and 0 is not there, then Min should be 0.

Scnario 2 : If the depth value is Negative to positive values (-5,-2, 0, 1,5,8 ). In this case the Minimum value is 1. How to achieve this scenario. (SRPNO = 1001)

image.png

[Abhishek_Hazra · Accepted solution]

Hi jelina.masilamani,

This is a bit complex requirement to achieve by only graphical calculation view. On the other side, can be easily done by a scripted one (table function based). You can simply create a table function based on case based select statement for your scenarios to derive different MIN() values for column depth.

If you still want it to be done only using graphical calculation view, below is the approach you can follow.

Notice there is no filter on Projection_1 & Projection_2 nodes unlike your screenshot. Projection_1 contains all 3 fields from your table SRPNO, VEHICLE_POSITION & DEPTH. Projection_2 contains just SRPNO & DEPTH.

On node Aggregation_1, I added DEPTH as aggregated column twice (right click & select Add as aggregated column), one of the aggregated depth has aggregation type MIN & other one MAX.

I defined all the possible scenarios you can expect from your dataset. I created a calculated column called SCENARIO (VARCHAR 1) for that.

The calculated column SCENARIO is defined like below : (It has 3 possible values : '1', '2' or '3' based on 3 different scenarios.)

1st scenario : when all the depth values are 0 or negative for a particular SRPNO -> This can be defined by MAX(DEPTH) <= 0 : In this case, you want to set the minimum depth as 0.

2nd scenario : when a particular SRPNO has a range of DEPTH combining negative & positive values : In this case, you want to set the minimum depth as the minimum non zero positive value.

3rd scenario : when a particular SRPNO has all positive DEPTH values : In this case it should simply consider the minimum positive value as minimum DEPTH.

The definition of the scenario column is as below :

if("DEPTH_MAX"<= 0,'1',if(("DEPTH_MIN" <= 0 and "DEPTH_MAX" > 0),'2','3'))

Once I join the Aggregation_1 node back with the original dataset from Projection_1 in Join_1 node, I get the entire dataset along with the calculated column : SCENARIO. Now I split the entire dataset based on a filter on SCENARIO column into 3 separate Projection nodes (in first screenshot : Scenario_1, Scenario_2 & Scenario_3).

Scenario_1 Projection node has SRPNO & filter on SCENARIO = '1', we don't need the column DEPTH for scenario 1 since we know the minimum for scenario 1 will always be 0.

So, right after Scenario_1 we have Aggregation_2, where we create a calculated column of type decimal called MIN_DEPTH & keep a constant value 0 for the definition.

Next we have the Scenario_2 projection node, where we need the minimum depth as the minimum non zero positive value, so I have kept a filter on DEPTH > 0 & SCENARIO = '2' in this node.

For the following Aggregation_3 node we only have positive DEPTH values & we want the lowest of these values, so in Aggregation_3 node I added DEPTH as an Aggregated column with aggregation type MIN.

The last scenario is the case where all depth values are positive & we take just the minimum value in this case, so in Scenario_3 node, we have a filter on SCENARIO = '3' & simply select the DEPTH_MIN column created in lower Aggreation_1 node.

Now in Aggregation_4 node, just select SRPNO & DEPTH_MIN as output columns.

All these 3 aggregation nodes based on 3 scenarios are put through a Union node with only 2 columns as output : SRPNO & MIN_DEPTH.

Now the final step is to use another join node : Join_2 to join this Union node back with original dataset from Join_1.

Just select the relevant fields as output.

I created a test table with dataset fulfilling all 3 scenarios you can expect as per your description.

Dataset & final output from calculation view :

Try to test this approach in your system. 🙂

Best Regards,
Abhi

[former_member184624 · Accepted solution]

Many Thanks Abhi. If possible, Can you pls share the sample code of table function.

[Abhishek_Hazra · Accepted solution]

Hi,

It would be like below & you can consume the table function directly in your calculation view as a node.

FUNCTION "TEST_TABLE_FUNC" ( ) 
	RETURNS TABLE
	(
	SRPNO VARCHAR(4),
	VEHICLE_POSITION VARCHAR(4),
	DEPTH DECIMAL(17,2),
	MIN_DEPTH DECIMAL(17,2)
	)
	LANGUAGE SQLSCRIPT
	SQL SECURITY INVOKER AS
BEGIN

s_1 = select "SRPNO" from <TABLE_NAME> group by "SRPNO" having max("DEPTH") <= 0 ;
s_2 = select "SRPNO" from <TABLE_NAME> group by "SRPNO" having min("DEPTH") <=0 and max("DEPTH") > 0;
s_3 = select "SRPNO" from <TABLE_NAME> group by "SRPNO" having min("DEPTH") > 0;

u = select "SRPNO", 0 as "MIN_DEPTH" from :s_1
      union all
      select a."SRPNO" as "SRPNO", b."MIN_DEPTH" as "MIN_DEPTH" from :s_2 a 
      inner join (select "SRPNO", min("DEPTH") as "MIN_DEPTH" from <TABLE_NAME> where "DEPTH" > 0 group by "SRPNO") b
      on a."SRPNO" = b."SRPNO"
      union all
      select c."SRPNO",  d."MIN_DEPTH" from :s_3 c 
      inner join (select "SRPNO", min("DEPTH") as "MIN_DEPTH" from <TABLE_NAME> group by "SRPNO") d
      on c."SRPNO" = d."SRPNO";
      

RETURN

select t1.*,t2."MIN_DEPTH" from <TABLE_NAME> t1 inner join :u t2
        on t1."SRPNO" = t2."SRPNO";


END;

Best Regards,
Abhi