Find minimum in Excel Value with For Loop

Help Orchestrator
, , , ,
1

Hi,

I have this Excel file as shown:
image

I need to find the minimum in the “Sales” column for a particular month.
For example: if the month we want is “June” then the minimum value of sales is “1043.86”

I have already design a workflow(a For Each loop) which filter for a particular company and for a particular month:

image
image879×465 12.9 KB

in_company is a variable set by the human for the company desired. For example here, it is “Thoughtmix”.

Can someone help me to find the minimum in sales?
Thanks!

1 Like
2

If i were you.

I would create a list variable, inside the loop check if sales column matches the required month and than add that sales amount to the list.

Once done convert the list to array and sort the array in ascending order, that way the first item in array would be the smallest number.

You could then just use array(0).ToString to get the smallest value.

3

@Yudhisteer_Chintaram1,

You can try with this Linq to find the minimum value from a datatable column

yourDataTable.AsEnumerable().Where(Function(f) Convert.ToString(f.Field(Of Object)("Month")).Trim.ToLower.Equals("yourMonthValue")).Min(Function(r) Convert.ToDouble(r.Field(Of Object)("Sales")))
1 Like
4

Should this be in an Assign activity?

5

What you think about sorting the table and pick the first row?

6

No. Because I have other activities to do below. :slight_smile:

7

Can you describe what else you need to to? Of course you need to filter the table for the requested month before.

8

Oh yes. I already filtered for the month and Company. How would you do the sorting?

9

As usual with the activity:

10

Create a variable of type List(Of Double) and in every loop you add the value in the collection, after you can get it like this:

Dim RowindexMin As Double = (From row As RowsFound In MyList.OrderBy(Function(x) x.LineValue) Select row.RowIndex).First

or

Use Bala Rev Activities or use the dT Query

intminvalue=Convert.ToInt32(dt.AsEnumerable().Max(Function(row) row(“Column2”)))

or

Before the loop you assign: MyList = new List(Of Double)
Inside the loop you add the values using a Add To Collection activity.
After the end of loop you assign:
MySmallValue = MyList.ToArray.Min()

or

=min(enter range of numbers).