Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!!!!
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.
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!!!!
Maybe: Count(distinct ID & Move)
- Marcus
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:
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.
What returned: Count(distinct [Position ID]) ?
- Marcus