Trying to Ignore Duplicate Values

[biddynamics]

I'm using the DUAL function to list % Ranges in a table:

=If((((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) < 0.01, Dual('0 to 1 %', 1),
If((((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) >= 0.01 and (((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) < 0.02, Dual('1 to 2 %', 2),
If((((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) >= 0.02 and (((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) < 0.03, Dual('2 to 3 %', 3),
If((((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) >= 0.03 and (((if(([Bid.Rank]>1), [Bid_Amount]))-(if(([Bid.Rank]>1), [Low_Comp_Bid1])))/ (if(([Bid.Rank]>1), [Bid_Amount]))) < 0.04, Dual('3 to 4 %', 4),))))

My problem is that I have duplicate values in one of the other columns (bid_amount) so when I select one of these % ranges, it will show all the ranges that has that duplicate value.  I only want it to display the row of the % range selected.

So say there are 2 records that have a bid_amount of 10.  But one falls in the 0-1% range and one falls in the 1-2% range.  When I select 0-1% i still see the 1-2% range in the table.  Hope this makes sense.

Thanks!

[morgankejerhag]

Hi! What is it that you want to accomplish with the expression?

[biddynamics]

when I select a range it selects the bid amounts in that range. If there is the same bid amount in another range, it also lists that range. I don’t want that to happen.

[biddynamics]

I want the expression to select unique [contract_id] 's for the range that is selected.  Right now it is selecting other variables that may have duplicates.

[Chanty4u]

May be you can create unique key field 

LOAD  

    *,  

    [Bid.Rank] & '-' & [Bid_Amount] & '-' & [Bidder] AS Bid_Key  

RESIDENT Table;

[morgankejerhag]

What is the percentage you are calculating?

[biddynamics]

The percentage by which a bid was lost by.

(Bid_Amount - Winning_Competitor_Bid) / (Bid_Amount) for all bids that were lost ie rank >1.

[morgankejerhag]

Maybe something like:

DataIn:

Load * INLINE [

Tender, Supplier, Bid

    1000, A, 345

    1000, B, 300

    1000, C, 370

    2000, A, 560

    2000, B, 600

    2000, C, 700

    3000, A, 900

    3000, B, 800

    3000, C, 500

];

DataWithWinner:

Load

*,

    if(peek(Tender)<>Tender,1,peek(Rank)+1) as Rank

resident DataIn order by Tender, Bid asc;

drop table DataIn;

left join

Load

Tender,

    Bid as WinningBid

resident DataWithWinner where Rank=1;

DataClass:

Load

*,

if(Rank=1,dual('Winner',0),class(Percentage,1)) as PercentageClass

;

Load

*,

    100*(Bid-WinningBid)/WinningBid as Percentage

resident DataWithWinner;

drop table DataWithWinner;