Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rviel_dubo
New Contributor III

Select Max Value from a list with condition

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

1 Solution

Accepted Solutions

Re: Select Max Value from a list with condition

Try this expression:

=Max({<Status = {'Win'}>}TOTAL <Project, Vendor> Aggr(Sum({<Status = {'Win'}>}BidAmt), Customer, Vendor, Project))

Capture.PNG

5 Replies

Re: Select Max Value from a list with condition

Try this expression:

=Max({<Status = {'Win'}>}TOTAL <Project, Vendor> Aggr(Sum({<Status = {'Win'}>}BidAmt), Customer, Vendor, Project))

Capture.PNG

rviel_dubo
New Contributor III

Re: Select Max Value from a list with condition

Hi Sunindia,

That exactly the statement I needed

Thx a lot

Robert

Re: Select Max Value from a list with condition

No problem Robert

I am glad I was able to help.

Best,

Sunny

rviel_dubo
New Contributor III

Re: Select Max Value from a list with condition

I was too fast on my response.

Your statement is good as long as there is a winner but it wont show projects in pending state

So I added 'Pending' to the set analysis you've provided to gain the right result.

Thx again, you've been a great help showing the way

Robert

Re: Select Max Value from a list with condition

Your sample did not have Pending Status and you did not mention that you wanted to include pending, so it was not possible for me to know

But I am glad you were able to figure it out.

Best,

Sunny

Community Browser