Solved: Re: Creating a Table grouping data by two variable... - Qlik Community

Daniel_de_Wet · ‎2023-01-27

Hi,

I need some help to create a table grouping data by two variables.

I have this table:

I would like to count the number of entries/events per ID and YEAR

The result should look like this:

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:

Why does this happen and what should i do to get the desired result?

Thanks for your help

Daniel

edwin · ‎2023-01-27

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

edwin · ‎2023-01-27

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

Daniel_de_Wet · ‎2023-01-28

Thanks Edwin!!!

I just missed it.... Thanks!!!

Creating a Table grouping data by two variables