Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need some help to create a table grouping data by two variables.
I have this table:
ID | YEARPREST | DATEPREST | TYPE_PREST |
a | 2018 | 14/04/2018 | [POST-PC] - 1.01.02 |
a | 2019 | 01/03/2019 | [POST-PC] - 1.01.03 |
a | 2019 | 02/03/2019 | [POST-PC] - 0. |
a | 2019 | 02/03/2019 | [POST-PC] - 1.03.01 |
a | 2019 | 02/03/2019 | [POST-PC] - 1.03.01 |
a | 2019 | 12/03/2019 |
[POST-PC] - 1.01.02 |
a | 2019 | 28/03/2019 | [POST-PC] - 1.01.03 |
a | 2019 | 28/03/2019 | [POST-PC] - 1.09.01 |
a | 2019 | 30/04/2019 | [POST-PC] - 1.01.03 |
a | 2019 | 30/04/2019 | [POST-PC] - 1.09.01 |
a | 2019 | 10/05/2019 | [POST-PC] - 1.01.03 |
a | 2019 | 20/05/2019 | [POST-PC] - 1.01.03 |
a | 2019 | 31/07/2019 | [POST-PC] - 1.01.03 |
a | 2019 | 11/09/2019 | [POST-PC] - 1.01.03 |
a | 2020 | 23/07/2020 | [POST-PC] - 1.01.03 |
a | 2020 | 25/08/2020 | [POST-PC] - 0. |
a | 2020 | 02/09/2020 | [POST-PC] - 1.01.03 |
a | 2020 | 23/11/2020 | [POST-PC] - 0. |
a | 2021 | 15/04/2021 | [POST-PC] - 0. |
a | 2021 | 15/05/2021 | [POST-PC] - 0. |
a | 2021 | 15/05/2021 | [POST-PC] - 1.02.01 |
a | 2021 | 23/07/2021 | [POST-PC] - 0. |
a | 2021 | 23/07/2021 | [POST-PC] - 1.01.02 |
a | 2021 | 23/07/2021 | [POST-PC] - 1.02.01 |
a | 2021 | 10/08/2021 | [POST-PC] - 0. |
a | 2021 | 28/08/2021 | [POST-PC] - 0. |
a | 2021 | 29/11/2021 | [POST-PC] - 0. |
a | 2022 | 24/02/2022 | [POST-PC] - 0. |
a | 2022 | 23/04/2022 | [POST-PC] - 0. |
a | 2022 | 23/04/2022 | [POST-PC] - 1.02.01 |
I would like to count the number of entries/events per ID and YEAR
The result should look like this:
ID | YEARPREST | NR_EVENT |
a | 2018 | 1 |
a | 2022 | 3 |
a | 2020 | 4 |
a | 2021 | 9 |
a | 2019 | 13 |
I have tried this:
EVENT_COUNT:
LOAD
ID,
Count(ID) as NR_EVENT,
Resident
EVENT Group by ID, YEARPREST;
The result I get from this is:
ID | YEARPREST | NR_EVENT |
a | 2018 | 1 |
a | 2018 | 3 |
a | 2018 | 4 |
a | 2018 | 9 |
a | 2018 | 13 |
a | 2019 | 1 |
a | 2019 | 3 |
a | 2019 | 4 |
a | 2019 | 9 |
a | 2019 | 13 |
a | 2020 | 1 |
a | 2020 | 3 |
a | 2020 | 4 |
a | 2020 | 9 |
a | 2020 | 13 |
a | 2021 | 1 |
a | 2021 | 3 |
a | 2021 | 4 |
a | 2021 | 9 |
a | 2021 | 13 |
a | 2022 | 1 |
a | 2022 | 3 |
a | 2022 | 4 |
a | 2022 | 9 |
a | 2022 | 13 |
Why does this happen and what should i do to get the desired result?
Thanks for your help
Daniel
when you load an aggregation, all the fields must be in the dimension:
EVENT_COUNT:
LOAD
ID,YEARPREST
Count(ID) as NR_EVENT,
Resident
EVENT Group by ID, YEARPREST;
if you just missed it just add it in. but if you intentionally skipped it bec of synthetic joins then you need more work on it
when you load an aggregation, all the fields must be in the dimension:
EVENT_COUNT:
LOAD
ID,YEARPREST
Count(ID) as NR_EVENT,
Resident
EVENT Group by ID, YEARPREST;
if you just missed it just add it in. but if you intentionally skipped it bec of synthetic joins then you need more work on it
Thanks Edwin!!!
I just missed it.... Thanks!!!