getting Maximum Absolute Value and its other related value

Question

I have a dataTable like this

Substation      ColumnTitle     Voltage    ptime     MW
-------------------------------------------------------------
A-1                A-741          400       00:00    -23.76
A-1                A-741          400       00:01    20.54
A-1                A-741          400       00:02    -19.09
A-1                A-741          400       00:03    -13.23
A-1                A-741          400       00:04    15.27
A-1                A-741          400       00:05    -12.7
A-2                A-741          400       00:00    -33.76
A-2                A-741          400       00:01    30.54
A-2                A-741          400       00:02    -49.09
A-2                A-741          400       00:03    -23.23
A-2                A-741          400       00:04    25.27
A-2                A-741          400       00:05    -22.7

I want to have all records for Maximum value of MW Field and its related ptime. So i have written this

   var highest = from e in dtReport.AsEnumerable()
                              group e by new {
                                  substation =e.Field<string>("substation"),
                                  ColumnTitle = e.Field<string>("ColumnTitle"),
                                  Voltage = e.Field<string>("Voltage")
                              }
                                  into dptgrp
                              let topsal = dptgrp.Max(x => decimal.Parse(x.Field<string>("MW")))
                              select new
                              {
                                  substation = dptgrp.Key.substation,
                                  ColumnTitle = dptgrp.Key.ColumnTitle,
                                  Voltagee = dptgrp.Key.Voltage,
                                  ptime = dptgrp.First(y => y.Field<string>("MW") == topsal.ToString()).Field<string>("ptime"),
                                  MW = topsal
                              };

the problem is that I want to have the maximum of absolute value of MW. I know I can have the absolute value by Math.Abs(). and it would be like that:

    let topsal = dptgrp.Max(x => Math.Abs(decimal.Parse(x.Field<string>("MW"))))

but in that case how can I have the related value of its ptime, I mean this line

   ptime = dptgrp.First(y => y.Field<string>("MW") == topsal.ToString()).Field<string>("ptime")

For example. for A-1,A741,400 , the Maximum Vlaue is -23.76. How can I have its ptime which is "00:00"?

Answer 1

Not sure this answer is careful enough, but within each grouping, order by the absolute value of interest and take .First(), like this:

var listOfItemsAttainingMaxMWWithingTheirGroup = dtReport.AsEnumerable()
  .GroupBy(e => new {
    substation =e.Field<string>("substation"),
    ColumnTitle = e.Field<string>("ColumnTitle"),
    Voltage = e.Field<string>("Voltage"),
  })
  .Select(grp => grp
    .OrderByDescending(e => Math.Abs(decimal.Parse(e.Field<string>("MW"))))
    .First())
  .ToList();

Answer 2

This is working fine.

assume that this is your database table entity

 class Table
    {
        public string Substation { get; set; }
        public string ColumnTitle { get; set; }
        public string ptime { get; set; }
        public string MW { get; set; }
        public string Voltage { get; set; }

    }

and the table contains two rows like this

 List<Table> dptgrp= new List<Table>();
        dptgrp.Add(new Table
        {
            ColumnTitle = "A-741",
            MW = "-23.76",
            ptime = "00:00",
            Substation = "",
            Voltage = "400"
        });
        dptgrp.Add(new Table
        {
            ColumnTitle = "A-741",
            MW = "20.54",
            ptime = "00:01",
            Substation = "",
            Voltage = "400"
        });

then you can get the result you want by the following code

    var maxValue = dptgrp.OrderBy(x => x.MW).FirstOrDefault().MW; // here you can use OrderByDescending  if you want to ignore the +/- signs
            var result = dptgrp.First(x => x.MW == maxValue).ptime; // this will be your final result

Answer 3

If I understand your question correctly: you want for each Substation all the records containing the maximum absolute value of MW.

You should start by grouping by Substation and getting the maximum MW value for all. With the Substation/MW table, you join with the original table where Substation and MW columns are the same.