Double key fields

rdomenoqv — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Double key fields

m_woolf — Wed, 08 Nov 2017 14:44:43 GMT

Can't you just do count(distinct Id_unique) in your last table?

Re: Double key fields

rdomenoqv — Wed, 08 Nov 2017 15:19:30 GMT

Yes, I tried, but did not work

Re: Double key fields

vinieme12 — Wed, 08 Nov 2017 17:00:18 GMT

what you have now done is absolutely correct, you must never use aggregation on KeyFields.

Having a separate field to use for such operations is the best thing to do.

Re: Double key fields

rdomenoqv — Wed, 08 Nov 2017 17:25:23 GMT

Thanks Vineeth; anyway I am still trying (perhaps any code line not well defined...)

topic Re: Double key fields in QlikView

Double key fields

Re: Double key fields

Re: Double key fields

Re: Double key fields

Re: Double key fields