cancel
Showing results for 
Search instead for 
Did you mean: 

Min() greater than zero.

former_member184624
Participant
0 Kudos

Dear HANA Experts,

I want to display the minimum value which should be greater than 0 in the calculated column. In my data set, i have a data from positive to negative values. By using the Minimum() function, I want to display greater than zero values only.

For Example the dataset is -12, 10, 5, 0,1. I am expecting the value as 1. Can you please guide me, how to achieve this requirement.

Thanks.

Accepted Solutions (1)

Accepted Solutions (1)

Abhishek_Hazra
Active Contributor
0 Kudos

Hi,

You can not use the MIN() function on the values under same column for different records since it operates on the values for same row. You need to build a table function using a select statement with where clause on the field as > 0 & include the table function inside the graphical calculation view.

The other alternative, if you only want to do it completely in graphical way is to create a separate aggregation branch & join back with the dataset.

You can follow the steps as below :

Dataset:

Make a calculation view like below split the dataset into a separate projection node only number in the output, which you want to have a min() on. Filter the number > 0.

Aggregation node :

Add the Number field in an aggregation node on top of the projection node & set the aggregation as Min as shown.

Join : Join the original dataset (Projection_1) as left table with the Aggregation_1 node as right table, don't provide any join condition.

Select the fields from right table in final default Aggregation node. This should give you the minimum positive number as NUM_1 field shows in the above screen shot.

Output : Should show minimum positive integer as you seek, ie. 1 for teh given dataset.

Hope this helps 🙂


Best Regards,
Abhi

Answers (4)

Answers (4)

former_member184624
Participant
0 Kudos

Hi Abhi,

Thank you. It is working as expected. There is one more case i need to cover.

For Example the dataset is -12, -1, -5, 0.(All the data are less than 1. In this case, I need to exclude these records in the filter itself) In this case, Can i apply the condition in Projection 1 itself ? Please suggest.

Thanks.

Abhishek_Hazra
Active Contributor
0 Kudos

Hi jelina.masilamani ,

Correct, if you want the records to get filtered out completely then you can place the filter on dataset as > 0 at node Projection_1 itself.
Also, if you find the original answer helpful, it will be appreciated if you accept that as correct answer.

Thanks,
Abhi

ThorstenHoefer
Active Contributor
0 Kudos

Hi,

can you use the aggregation function sum over GREATEST ?

sum( GREATEST(amount,0))
former_member184624
Participant
0 Kudos

Thank you Hofer. I think, this logic i can write in scripted calculation view.

former_member184624
Participant
0 Kudos

Hi Venkat, Thank you for providing SQL. But i want the logic to be applied in single field.

Hi Abhi, Thank you for the detailed step. I will update you once i created the flow.

Thanks

venkateswaran_k
Active Contributor

My Query will work for single field also.. you may remove the negative column in my example.

venkateswaran_k
Active Contributor
0 Kudos

Hi

Try this

<TABLE> - is your table

<COL> is your column

SELECT COL2, MIN(NEGATIVE) AS "MIN_OF_NEG",
             MIN(POSITIVE) AS "MIN_OF_POS"
FROM
(
SELECT CASE WHEN COL < 0  THEN COL ELSE 0 END AS "NEGATIVE",
       CASE WHEN COL >= 0 THEN COL ELSE 0 END AS "POSTIVIE"
FROM <TABLE>
)
GROUP BY COL2.