5 Replies Latest reply: Jun 29, 2015 10:29 AM by Sunny Talwar RSS

    Select Max Value from a list with condition

    Robert Viel

      Hi everyone,

       

       

      I have a table with these dimensions

        Project, Vendor, Customer, Item, Status

      and BidAmt as expression.

       

      Table:

      LOAD * Inline [

        Project,     Vendor,    Customer,       Item,     BidAmt,    Status

        1A,           2,               B,                    No1,     10,            Win

        1A,           2,               B,                    No2,     3,              Win

        1A,           2,               D,                    No3,    12,             Lost

        1A,           2,               A,                    No4,     7,              Lost

        1A,           5,               B,                    Gl4,      6,              Win

        1A,           5,               B,                    Gl5,      1,              Win

        1A,           5,               A,                    Gl6,      8,              Lost

        1A,           5,               D,                    Gl4,      7,              Lost

        1A,           8,               A,                    Dw1,    10,            Lost

        1A,           8,               D,                    Dw6,    9,              Lost

        1A,           8,               B,                    Dw6,    9,               Win

        2C,           2,               D,                    No7,    15,             Win

        2C,           2,               E,                    No6,     8,              Lost

        2C,           2,               E,                    No4,     6,              Lost

        2C,           4,               F,                    Kn6,     9,               Lost

        2C,           4,               D,                    Kn8,    13,             Win

        2C,           4,               E,                    Kn8,    13,             Lost

        2C,           4,               D,                    Kn3,    3,               Win

        2C,           4,               F,                    Kn4,    15,              Lost

        2C,           6,               E,                    Th2,    25,              Lost

        2C,           6,               F,                    Th2,    22,              Lost

        2C,           6,               D,                    Th6,   17,               Win

      ];

       

      I need to find out the highest bid value of a project per vendor unless it has been won by a customer.

      I've used this statement to get the highest bid value

       

      =Max(TOTAL <Project, Vendor> Aggr(Sum(BidAmt), Customer, Vendor, Project))

       

      but I just can't get the winning value

      I've tried using IF statement but didn't worked the way I wanted to.

       

      I get this:

      Bid1.png

      But would like to show this:

      Bid1.png

      Any help would be grantly appreciated.

       

      Thanks.

       

      Robert