topic Re: Creating a Table grouping data by two variables in App Development

topic Re: Creating a Table grouping data by two variables in App Development https://community.qlik.com/t5/App-Development/Creating-a-Table-grouping-data-by-two-variables/m-p/2031073#M84923 <P>when you load an aggregation, all the fields must be in the dimension:<BR /><BR /></P> <LI-CODE lang="markup">EVENT_COUNT: LOAD ID,YEARPREST Count(ID) as NR_EVENT, Resident EVENT Group by ID, YEARPREST;</LI-CODE> <P>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</P> Sat, 28 Jan 2023 01:33:18 GMT edwin 2023-01-28T01:33:18Z Creating a Table grouping data by two variables https://community.qlik.com/t5/App-Development/Creating-a-Table-grouping-data-by-two-variables/m-p/2030990#M84919 <P>Hi, </P> <P>I need some help to create a table grouping data by two variables.</P> <P>I have this table: </P> <TABLE width="625"> <TBODY> <TR> <TD width="96">ID</TD> <TD width="96">YEARPREST</TD> <TD width="96">DATEPREST</TD> <TD width="337">TYPE_PREST</TD> </TR> <TR> <TD>a</TD> <TD>2018</TD> <TD>14/04/2018</TD> <TD>[POST-PC] - 1.01.02</TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>01/03/2019</TD> <TD>[POST-PC] - 1.01.03  </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>02/03/2019</TD> <TD>[POST-PC] - 0. </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>02/03/2019</TD> <TD>[POST-PC] - 1.03.01  </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>02/03/2019</TD> <TD>[POST-PC] - 1.03.01  </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>12/03/2019</TD> <TD> <P>[POST-PC] - 1.01.02</P> </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>28/03/2019</TD> <TD>[POST-PC] - 1.01.03  </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>28/03/2019</TD> <TD>[POST-PC] - 1.09.01  </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>30/04/2019</TD> <TD>[POST-PC] - 1.01.03  </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>30/04/2019</TD> <TD>[POST-PC] - 1.09.01 </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>10/05/2019</TD> <TD>[POST-PC] - 1.01.03  </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>20/05/2019</TD> <TD>[POST-PC] - 1.01.03  </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>31/07/2019</TD> <TD>[POST-PC] - 1.01.03  </TD> </TR> <TR> <TD>a</TD> <TD>2019</TD> <TD>11/09/2019</TD> <TD>[POST-PC] - 1.01.03  </TD> </TR> <TR> <TD>a</TD> <TD>2020</TD> <TD>23/07/2020</TD> <TD>[POST-PC] - 1.01.03  </TD> </TR> <TR> <TD>a</TD> <TD>2020</TD> <TD>25/08/2020</TD> <TD>[POST-PC] - 0. </TD> </TR> <TR> <TD>a</TD> <TD>2020</TD> <TD>02/09/2020</TD> <TD>[POST-PC] - 1.01.03  </TD> </TR> <TR> <TD>a</TD> <TD>2020</TD> <TD>23/11/2020</TD> <TD>[POST-PC] - 0. </TD> </TR> <TR> <TD>a</TD> <TD>2021</TD> <TD>15/04/2021</TD> <TD>[POST-PC] - 0. </TD> </TR> <TR> <TD>a</TD> <TD>2021</TD> <TD>15/05/2021</TD> <TD>[POST-PC] - 0. </TD> </TR> <TR> <TD>a</TD> <TD>2021</TD> <TD>15/05/2021</TD> <TD>[POST-PC] - 1.02.01  </TD> </TR> <TR> <TD>a</TD> <TD>2021</TD> <TD>23/07/2021</TD> <TD>[POST-PC] - 0. </TD> </TR> <TR> <TD>a</TD> <TD>2021</TD> <TD>23/07/2021</TD> <TD>[POST-PC] - 1.01.02</TD> </TR> <TR> <TD>a</TD> <TD>2021</TD> <TD>23/07/2021</TD> <TD>[POST-PC] - 1.02.01  </TD> </TR> <TR> <TD>a</TD> <TD>2021</TD> <TD>10/08/2021</TD> <TD>[POST-PC] - 0. </TD> </TR> <TR> <TD>a</TD> <TD>2021</TD> <TD>28/08/2021</TD> <TD>[POST-PC] - 0. </TD> </TR> <TR> <TD>a</TD> <TD>2021</TD> <TD>29/11/2021</TD> <TD>[POST-PC] - 0. </TD> </TR> <TR> <TD>a</TD> <TD>2022</TD> <TD>24/02/2022</TD> <TD>[POST-PC] - 0. </TD> </TR> <TR> <TD>a</TD> <TD>2022</TD> <TD>23/04/2022</TD> <TD>[POST-PC] - 0. </TD> </TR> <TR> <TD>a</TD> <TD>2022</TD> <TD>23/04/2022</TD> <TD>[POST-PC] - 1.02.01  </TD> </TR> </TBODY> </TABLE> <P> </P> <P>I would like to count the number of entries/events per ID and YEAR</P> <P>The result should look like this:</P> <TABLE width="389"> <TBODY> <TR> <TD width="261">ID</TD> <TD width="64">YEARPREST </TD> <TD width="64">NR_EVENT </TD> </TR> <TR> <TD>a</TD> <TD width="64">2018</TD> <TD width="64">1</TD> </TR> <TR> <TD>a</TD> <TD width="64">2022</TD> <TD width="64">3</TD> </TR> <TR> <TD>a</TD> <TD width="64">2020</TD> <TD width="64">4</TD> </TR> <TR> <TD>a</TD> <TD width="64">2021</TD> <TD width="64">9</TD> </TR> <TR> <TD>a</TD> <TD width="64">2019</TD> <TD width="64">13</TD> </TR> </TBODY> </TABLE> <P> </P> <P>I have tried this:</P> <P>EVENT_COUNT:<BR />LOAD</P> <P>ID,<BR />Count(ID) as NR_EVENT,</P> <P>Resident</P> <P>EVENT Group by ID, YEARPREST;</P> <P>The result I get from this is:</P> <TABLE width="389"> <TBODY> <TR> <TD width="261">ID</TD> <TD width="64">YEARPREST </TD> <TD width="64">NR_EVENT </TD> </TR> <TR> <TD>a</TD> <TD width="64">2018</TD> <TD width="64">1</TD> </TR> <TR> <TD>a</TD> <TD width="64">2018</TD> <TD width="64">3</TD> </TR> <TR> <TD>a</TD> <TD width="64">2018</TD> <TD width="64">4</TD> </TR> <TR> <TD>a</TD> <TD width="64">2018</TD> <TD width="64">9</TD> </TR> <TR> <TD>a</TD> <TD width="64">2018</TD> <TD width="64">13</TD> </TR> <TR> <TD>a</TD> <TD width="64">2019</TD> <TD width="64">1</TD> </TR> <TR> <TD>a</TD> <TD width="64">2019</TD> <TD width="64">3</TD> </TR> <TR> <TD>a</TD> <TD width="64">2019</TD> <TD width="64">4</TD> </TR> <TR> <TD>a</TD> <TD width="64">2019</TD> <TD width="64">9</TD> </TR> <TR> <TD>a</TD> <TD width="64">2019</TD> <TD width="64">13</TD> </TR> <TR> <TD>a</TD> <TD width="64">2020</TD> <TD width="64">1</TD> </TR> <TR> <TD>a</TD> <TD width="64">2020</TD> <TD width="64">3</TD> </TR> <TR> <TD>a</TD> <TD width="64">2020</TD> <TD width="64">4</TD> </TR> <TR> <TD>a</TD> <TD width="64">2020</TD> <TD width="64">9</TD> </TR> <TR> <TD>a</TD> <TD width="64">2020</TD> <TD width="64">13</TD> </TR> <TR> <TD>a</TD> <TD width="64">2021</TD> <TD width="64">1</TD> </TR> <TR> <TD>a</TD> <TD width="64">2021</TD> <TD width="64">3</TD> </TR> <TR> <TD>a</TD> <TD width="64">2021</TD> <TD width="64">4</TD> </TR> <TR> <TD>a</TD> <TD width="64">2021</TD> <TD width="64">9</TD> </TR> <TR> <TD>a</TD> <TD width="64">2021</TD> <TD width="64">13</TD> </TR> <TR> <TD>a</TD> <TD width="64">2022</TD> <TD width="64">1</TD> </TR> <TR> <TD>a</TD> <TD width="64">2022</TD> <TD width="64">3</TD> </TR> <TR> <TD>a</TD> <TD width="64">2022</TD> <TD width="64">4</TD> </TR> <TR> <TD>a</TD> <TD width="64">2022</TD> <TD width="64">9</TD> </TR> <TR> <TD>a</TD> <TD width="64">2022</TD> <TD width="64">13</TD> </TR> </TBODY> </TABLE> <P> </P> <P>Why does this happen and what should i do to get the desired result? </P> <P>Thanks for your help </P> <P>Daniel</P> Fri, 27 Jan 2023 17:51:36 GMT https://community.qlik.com/t5/App-Development/Creating-a-Table-grouping-data-by-two-variables/m-p/2030990#M84919 Daniel_de_Wet 2023-01-27T17:51:36Z Re: Creating a Table grouping data by two variables https://community.qlik.com/t5/App-Development/Creating-a-Table-grouping-data-by-two-variables/m-p/2031073#M84923 <P>when you load an aggregation, all the fields must be in the dimension:<BR /><BR /></P> <LI-CODE lang="markup">EVENT_COUNT: LOAD ID,YEARPREST Count(ID) as NR_EVENT, Resident EVENT Group by ID, YEARPREST;</LI-CODE> <P>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</P> Sat, 28 Jan 2023 01:33:18 GMT https://community.qlik.com/t5/App-Development/Creating-a-Table-grouping-data-by-two-variables/m-p/2031073#M84923 edwin 2023-01-28T01:33:18Z Re: Creating a Table grouping data by two variables https://community.qlik.com/t5/App-Development/Creating-a-Table-grouping-data-by-two-variables/m-p/2031099#M84927 <P>Thanks Edwin!!!</P> <P>I just missed it.... Thanks!!!</P> Sat, 28 Jan 2023 07:28:21 GMT https://community.qlik.com/t5/App-Development/Creating-a-Table-grouping-data-by-two-variables/m-p/2031099#M84927 Daniel_de_Wet 2023-01-28T07:28:21Z