Hi:
I have this table with thousands of transactions:
[transactions]:
LOAD
---
---
ID_BUYER
---
ID_SELLER
---
---
The ID_BUYER and ID_SELLER are unique numbers and are assocciated to unique persons.
So, one person can act as ID_BUYER in some transactions, act as ID_SELLER in some others, and act as ID_BUYER and ID_SELLER in the same transactions (strange, but valid in context).
With this information, I built lot of charts, straight tables, etc. selecting proper transacciones (according to interval dates, amounts, places, type of transactions, etc. etc.) and everything went ok.
But, now I face this problem: need to show how many DIFFERENTS ids have in a determined selection; suppose this:
ID_BUYER ID_SELLER amount no.of tr.:
123 456 m1 t1
651 782 m2 t2
842 512 m3 t3
In this case:
Differents ID_BUYER = 3 come from (count(distinct(ID_BUYER))
Differents ID_SELLER = 3 come from (count(distinct(ID_SELLER))
Differents ID_BUYER or ID_SELLER = 6
and everything is ok
Now this case:
ID_BUYER ID_SELLER amount no.of tr.:
123 456 150 2
651 123 410 6
842 842 145 3
Differents ID_BUYER = 3
Differents ID_SELLER = 3
Differents ID_BUYER or ID_SELLER = ….should be 4, because two of them are equal…
but I can’t find any alternative to get 4; after lots of trials, I created new tables with unique keys:
[unique_ids]:
LOAD DISTINCT
ID_BUYER,
ID_BUYER as id_unique,
‘B’ as b_field
FROM …..
CONCATENATE
LOAD DISTINCT
ID_SELLER,
ID_SELLER as id_unique
‘S’ as s_field
FROM….
And I get the table:
Id_unique b_field s_field
123 B S
456 - S
651 B -
842 B S
The table looks correct, but then can’t find ways (with or without this last table) to get the number 4 (which be the total differents) for that selection, or at least 2 (which be the total equals); if I could get 2, I will add 4 (ID_BUYERs) to 4 (ID_SELLERs) minus 2 (the equals)
Any suggestions?
Thanks
