Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
robin_heijt
Creator
Creator

Count individual records in a joined table

Hi,

 

In my data fields I use 1 unique identifier, which normally I can calculate by using Count Distinct.

However in another situation I have 2 fields;

- ID

- Move

If my data is the following:

ID             Move

1                Promotion

2               Promotion

2               Promotion

3               Promotion

3                Promotion

4                Promotion

How would I count the total amount of promotions if the data comes from joined tables of thousands of records?

If I would use Count(ID) the result from this table would be like '200'(Due to the joined tables)

Count(Move) Same issue

And count distinct in this situation would not work, since I have duplicates in both fields.

 

Thanks.

 

2 Solutions

Accepted Solutions
marcos_rv_666
Contributor II
Contributor II

Hola, como estas?? 

Lo que no entiendo es que es lo que queres contar,  por un lado tenes la tabla 1 con los campos ID y MOVE, seguido tenes una segunda tabla (tabla 2) que tiene los mismos campos.

 

Ahora lo que vos queres es contar los diferentes combos ID-MOVE.

 

Por ejemplo:

ID             Move

1                Promotion

2               Promotion

2               Promotion

3               Promotion

3                Promotion

4                Promotion

4                No sale

 

El count ( ID ) = 7

count( distinct ID ) = 4 

Pero no se tiene en cuenta el campo MOVE.

sugiero armar un campo con los 2.

ID&'-'&MOVE as ID_MOVE

Ahora con este campo al hacer el count( ID_MOVE ) =7

count(distinct ID_MOVE ) = 5 .

Esto es lo que lo que buscabas??? para armar este campo deberias hacer un resident a la tabla con las el join.

 

Saludos!!!!

 

 

 

View solution in original post

marcus_sommer

Maybe: Count(distinct ID & Move)

- Marcus

View solution in original post

7 Replies
vishsaggi
Champion III
Champion III

Flag the tables with source. Like 

Table1: 

LOAD *,  1 AS TableSource

FROM table1;

Table2:

LOAD *, 2 AS TableSource

From Table2;

Count({<TableSource = {1} >}ID)

Count({<TableSource = {2} >}ID)

Something like this. 

marcos_rv_666
Contributor II
Contributor II

Hola, como estas?? 

Lo que no entiendo es que es lo que queres contar,  por un lado tenes la tabla 1 con los campos ID y MOVE, seguido tenes una segunda tabla (tabla 2) que tiene los mismos campos.

 

Ahora lo que vos queres es contar los diferentes combos ID-MOVE.

 

Por ejemplo:

ID             Move

1                Promotion

2               Promotion

2               Promotion

3               Promotion

3                Promotion

4                Promotion

4                No sale

 

El count ( ID ) = 7

count( distinct ID ) = 4 

Pero no se tiene en cuenta el campo MOVE.

sugiero armar un campo con los 2.

ID&'-'&MOVE as ID_MOVE

Ahora con este campo al hacer el count( ID_MOVE ) =7

count(distinct ID_MOVE ) = 5 .

Esto es lo que lo que buscabas??? para armar este campo deberias hacer un resident a la tabla con las el join.

 

Saludos!!!!

 

 

 

vishsaggi
Champion III
Champion III

Can you translate this to English please...
marcus_sommer

Maybe: Count(distinct ID & Move)

- Marcus

robin_heijt
Creator
Creator
Author

Thanks for your responses, but I am not sure this is what I am looking for. Or I might misunderstand the logic.

 

Please take a look at this screenshot, and it might help you understand the situation:

Count.PNG

The result that I am looking for is to have Count(Re. Identifier) to show '3' in this scenario. But due to joined tables it shows '8'. And count distinct would not work in this scenario.

 

Thanks.

marcus_sommer

What returned: Count(distinct [Position ID]) ?

- Marcus

robin_heijt
Creator
Creator
Author

Thanks, this worked. By using ID and Date. I did not know this was possible to count for 2 values. Thanks a lot