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.
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
(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
]
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…