topic Re: Trying to Ignore Duplicate Values in App Development

Trying to Ignore Duplicate Values

biddynamics — Tue, 11 Feb 2025 16:38:19 GMT

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!

Re: Trying to Ignore Duplicate Values

morgankejerhag — Wed, 12 Feb 2025 05:18:09 GMT

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

Re: Trying to Ignore Duplicate Values

biddynamics — Wed, 12 Feb 2025 12:17:25 GMT

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.

Re: Trying to Ignore Duplicate Values

biddynamics — Wed, 12 Feb 2025 16:14:58 GMT

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.

Re: Trying to Ignore Duplicate Values

Chanty4u — Wed, 12 Feb 2025 18:05:59 GMT

May be you can create unique key field

LOAD

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

RESIDENT Table;

Re: Trying to Ignore Duplicate Values

morgankejerhag — Wed, 12 Feb 2025 20:09:15 GMT

What is the percentage you are calculating?

Re: Trying to Ignore Duplicate Values

biddynamics — Wed, 12 Feb 2025 20:27:47 GMT

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.

Re: Trying to Ignore Duplicate Values

morgankejerhag — Wed, 12 Feb 2025 21:45:21 GMT

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;