How to find Min / Max in a column

[msusmani]

I have a dataset in which daily receipts are recorded in the following fashion

Date / Customer ID / Receipt Number / Sale Amount

I want to find out which date had the maximum sales and which date had the minimum sale amount regardless of the customer.

My current flow is to use “Group By” to use Date as group column and sum(Sale Amount) in Aggregation. I have then used 2 Math Formula Nodes with the following conditions,

COL_MAX($Sum(Total_Sales)$) == $Sum(Total_Sales)$

and the other one is

COL_MIN($Sum(Total_Sales)$) == $Sum(Total_Sales)$

I have then used a Joiner class to merge the results.

My question, is there a simpler way of doing it?

TIA

[mlauber71]

@msusmani welcome to the KNIME forum. You could take a look at the Duplicate Row Filter which can select lines based on a 2nd criteria.

You would still have to process the results (merge).

aggregate data by ID and merge – mlauber71

[diego_rod_lop]

Hey @msusmani Thanks for posting about this topic.

If I understood correctly your goal, it could be accomplished using a Statistics node, that returns some statistics for your “Sale Amount” column, as the Min and Max.

Then you can convert these values in flow variables and use them in a Rule-based Row filter, see screenshot below

Hoping this is the solution that you were looking for, I don’t know if it’s a fancier one

I am also attaching an example workflow that you can download and execute.
how-to-find-min-max-in-a-column.knwf (280.8 KB)