Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
rdomenoqv
Contributor
Contributor

Double key fields

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

4 Replies
m_woolf
Master II
Master II

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

rdomenoqv
Contributor
Contributor
Author

Yes, I tried, but did not work

vinieme12
Champion II
Champion II

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.

rdomenoqv
Contributor
Contributor
Author

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