3 Replies Latest reply on Aug 31, 2016 6:36 AM by Nikita Goldovsky

# How to Select Only One Value Between Two

Hello-

I have a data set of opportunities with contract values. However, an opportunity may appear twice in my data set if the contacts that are associated with the opportunity have different "Contact Associated Types".

"Contact Associated Type" can take on the value of "Direct" or "Indirect"

Opportunity IDContact Associated TypeAmount
1234Direct\$6000
1234Indirect\$6000

I need some kind of logic that in cases when both Direct and Indirect are present in  "Contact Associated Type", the amount is only associated with the Direct entry.

If only indirect value exists for an opportunity ID, then continue to consider the amount.

Desired output example:

Opportunity IDContact Associated TypeAmount***New Amount
1234Indirect60000
1234Direct60006000
5678Indirect50005000
9999Direct40004000

Thank you.

• ###### 1. Re: How to Select Only One Value Between Two

(VALUE_A + (IF ISNULL(VALUE_B) THEN 0 ELSE VALUE_B END))

• ###### 2. Re: How to Select Only One Value Between Two

What you can do is count the distinct contact types for each ID. If the count is two then you can use logic for if direct then amount else null. If the count is 1 then amount.

See the the calculated fields "contact type count" and "new amount" in the attached.

contact type count:

{fixed [Opportunity ID]: COUNTD([Contact Associated Type])}

new amount:

if [contact type count] = 1 then [Amount]

ELSEIF [Contact Associated Type] = "Direct" then [Amount]

else null end

1 of 1 people found this helpful
• ###### 3. Re: How to Select Only One Value Between Two

This is the correct answer to my question.

Unfortunately, I realized that an opportunity can also have two contacts of the same contact associated type, in which case I also need to deduplicate the amount of value that is allocated across the contacts.

To do that, I added another calculated field counting just the direct opps.

Count of Direct Opp Types:

{ FIXED [Opportunity ID]: SUM(IF [Contact Associated Type]="Direct" THEN 1 ELSE 0 END)}

And added the following line of text to your "new amount" calculation.

ELSEIF [Count of Direct Opp Types] = 0 then [Amount]/[Contact Type Count]