Finding the highest value in excel

Hi,

I have the excel table with invoices for last year. I have to find the highest value for each of my clients. Please find attached the example table. My result shoud be:
Client X 2500
Client Y 3000
Client Z 4200
First I have to use counter of clients. But what’s the next step?

Of course my analize is much more complicated, but if I know the above solution, I will manage it.

The file of course:)

example.xlsx (15.6 KB)

You can use group by to get the required data with the max condition or as it is a excel file, you can simply pivot out the data and the aggregation rule should be max so that you will get the max value for each individual client

dt.Compute("MAX(Column2)", "[Column1]='text'").ToString

@Krzysztof

use within an assign and get back a datatable

(From r In dtData.AsEnumerable
Group r By PC=r(“Client”) Into grp=Group
Select grp.OrderBy(Function ( r ) CInt(r(“Invoice Value”).toString.Trim)).Last()).CopyToData

Result:
[Client,Invoice value
Client X,2500
Client Y,3000
Client Z,4200
]

Hi,

could you explain how to use it?

Column2 will be for you: [Invoice Value]
Column1 will be like: ‘Client X’
it will return the maximum value for each client.

I created:
dtTable(“MAX(Invoice value)”, “[Client]=‘strFirma’”).ToString
but it does not work. strFirma - it is name of company.

It is wrong…
dtTable.Compute("MAX([Invoice value])", "[Client]='strFirma'").ToString

I have the following wrong - I try to assign the result to str variable.
image

ok, then your column is not numeric, use like this:
dtTable.Compute(“MAX(Convert([Invoice value], ‘System.Int32’))”, “[Client]=‘strFirma’”).ToString

Hi @Krzysztof

Try this
Int MaxValue=Convert.ToInt32(dt.AsEnumerable().Max(Function(row) row("invoice value)))

Thanks
Ashwin.S

image

The strFirma is name of Client (eg. Client X, Client Y), not column.

i understand that, but i cant see what you wrote there…

@bcorrea

I tried your solution

inputDT.Compute("MAX(Convert([Invoice value], 'System.Int32'))", "[Client]='Client X'").ToString

However, this error happens
image

Do you have any idea of what is happening?

I guess is not possible to convert the column inside a compute function, the column would have to be numeric or even to add a temporary column of numeric type just for the calculation…

1 Like