How to get a unique occurrence per person

[#1]

I've got these fields : person_id, transaction_id and vendor_name.

How would I create expression in a straight table with only unique vendors (that are associated with only one person but do not appear with others)?

These vendors can have multiple transaction_ids with the same person but they can have transactions with ONLY that person.

[sunny_talwar]

May be using a set analysis

{<vendor_name = {"=Count(DISTINCT person_id) = 1"}>}

[#3]

Sunny, thank you for your reply but this is not working for some reason.

I wrapped it in the sum expression:

sum({<vendor_name={"=Count(DISTINCT person_id) =1"}>} trans_amt)

[sunny_talwar]

So when you have vendor as dimension, are you not seeing only those vendors which have served to one and only one person_id?

[#5]

No. And I would like to have both, person_id and vendor_name as dimensions.

[sunny_talwar]

Would you be able to share a sample where this isn't working?

[joseduque]

Hi Sunny,

This might help. You have to count from the script the clients that the vendor have:

Then Use Set Analysis:

Sum({<Client_Unique={1}>}Transaccion)

Just One Client per vendor

All the table

I attached the example. Hope This Helps

José

[#8]

Please see attached. In this case I would only need to see person_ids 3, 4 and 7 as they have unique vendors and to sum trans_amt.

I would like to avoid resident load , if possible, as the table is quite large.

[sunny_talwar]

How does 3, 4, & 7 have unique vendor here?

I think I am missing the logic here, but person_ID 3 seems to have 6 vendors. Why would he be part of the output?

[sunny_talwar · Accepted solution]

Try one of these:

Sum({<person_ID = p({<vendor_Name={"=Count(DISTINCT person_ID) =1"}>})>} trans_amt)

or

Sum({<vendor_Name = {"=Count(DISTINCT person_ID) =1"}>} trans_amt)

Be careful with case sensitivity of your field names, i.e., vendor_name is not the same as vendor_Name and person_id is not the same as person_ID

Attaching the sample back