Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
May be using a set analysis
{<vendor_name = {"=Count(DISTINCT person_id) = 1"}>}
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)
So when you have vendor as dimension, are you not seeing only those vendors which have served to one and only one person_id?
No. And I would like to have both, person_id and vendor_name as dimensions.
Would you be able to share a sample where this isn't working?
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é
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.
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?
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