Generating a count number for records with the same value in one column

draghici1109 — Sun, 23 Jan 2022 19:08:12 GMT

Hallo Community,

here is a challenge that I have in coding in Qlik View. As I am a beginner in Coding, I cannot write a code that should solve the following problem:

Using the code bellow:

PA2001_EZ_10:
LOAD PERNR, BEGDA, ENDDA
FROM
[..\DATA_10\PA2001_EZ_10.qvd] (qvd);

PA2001BA01:
NoConcatenate
LOAD PERNR, BEGDA, ENDDA
Resident PA2001_EZ_10
Order by PERNR;

I got the result:

PERNR	BEGDA	ENDDA
902251	21.10.2020	23.08.2022
902962	11.10.2015	10.12.2015
905136	03.07.2016	02.08.2016
905136	03.07.2017	02.08.2017
905136	12.09.2020	11.10.2020
905136	12.09.2021	11.10.2021
905732	02.03.2010	01.04.2010
905732	02.11.2010	01.12.2010
906190	06.03.2010	07.01.2013
906530	22.07.2014	21.09.2014
906530	04.04.2016	03.05.2016
906530	04.01.2017	03.02.2017
906621	25.08.2011	30.06.2013
906621	01.07.2013	28.06.2014
906633	09.09.2012	08.11.2012
906633	04.02.2015	03.04.2015
906797	17.06.2010	16.08.2010
906803	05.06.2011	31.03.2013
906803	06.08.2013	30.09.2016
906839	01.01.2010	31.07.2011
906980	26.05.2010	25.06.2010
906980	26.08.2010	25.09.2010
906980	13.02.2013	12.03.2013
907091	01.01.2010	20.08.2010
907091	21.08.2010	31.10.2012
907170	12.06.2012	11.08.2012
907170	12.11.2012	11.12.2012

My Goal is to generate the following table

PERNR	Attribute	BEGDA	ENDDA
902251	EZ1	21.10.2020	23.08.2022
902962	EZ1	11.10.2015	10.12.2015
905136	EZ1	03.07.2016	02.08.2016
905136	EZ2	03.07.2017	02.08.2017
905136	EZ3	12.09.2020	11.10.2020
905136	EZ4	12.09.2021	11.10.2021
905732	EZ1	02.03.2010	01.04.2010
905732	EZ2	02.11.2010	01.12.2010
906190	EZ1	06.03.2010	07.01.2013
906530	EZ1	22.07.2014	21.09.2014
906530	EZ2	04.04.2016	03.05.2016
906530	EZ3	04.01.2017	03.02.2017
906621	EZ1	25.08.2011	30.06.2013
906621	EZ2	01.07.2013	28.06.2014
906633	EZ1	09.09.2012	08.11.2012
906633	EZ2	04.02.2015	03.04.2015
906797	EZ1	17.06.2010	16.08.2010
906803	EZ1	05.06.2011	31.03.2013
906803	EZ2	06.08.2013	30.09.2016
906839	EZ1	01.01.2010	31.07.2011
906980	EZ1	26.05.2010	25.06.2010
906980	EZ2	26.08.2010	25.09.2010
906980	EZ3	13.02.2013	12.03.2013
907091	EZ1	01.01.2010	20.08.2010
907091	EZ2	21.08.2010	31.10.2012

meaning that in the column "Attribute" one should generate through itterations the string EZxx, where xx is the order number of the same PERNR.

Example:

905136	EZ1	03.07.2016	02.08.2016
905136	EZ2	03.07.2017	02.08.2017
905136	EZ3	12.09.2020	11.10.2020
905136	EZ4	12.09.2021	11.10.2021

In the second column, the 1 of EZ1 counts the first occurence of a record with the PERNR 905136. Consequently, the second record in the previous table contains the 2 for EZ2 due to the fact that this is the second record with the same value 905136 for PERNR and so on.

If the PERNR has a new value, different from the previus value of PERNR in the input table, than EZxx should be reset to EZ1.

How should the code in qlik looks like in order to generate the second column, containing the values EZxx?

Remark: I tried to write a code using PEEK and PREVIOUS, but have no clue how to generate xx in EZxx as a result of an itteration.

Any help / hint would be highly appreciated!

Kind Regards, Alexandru

Re: Generating a count number for records with the same value in one column

MarcoWedel — Sun, 23 Jan 2022 19:48:55 GMT

Hi,

one solution could be:

PA2001BA01: LOAD PERNR, 'EZ'&AutoNumber(RecNo(),PERNR) as Attribute, BEGDA, ENDDA FROM [https://community.qlik.com/t5/QlikView-App-Dev/Generating-a-count-number-for-records-with-the-same-value-in-one/td-p/1884207] (html, codepage is 1252, embedded labels, table is @1);

hope this helps

Marco

Re: Generating a count number for records with the same value in one column

draghici1109 — Mon, 24 Jan 2022 16:36:25 GMT

Thanks Marco!

This is the solution. I realize, I did not understand until now the function AutoNumber.

Kind Regards and Kudos!

Alexandru

Re: Generating a count number for records with the same value in one column

MarcoWedel — Mon, 24 Jan 2022 17:25:57 GMT

you're welcome

please close this thread by accepting a solution

thanks

topic Re: Generating a count number for records with the same value in one column in QlikView

Generating a count number for records with the same value in one column

How should the code in qlik looks like in order to generate the second column, containing the values EZxx?

Re: Generating a count number for records with the same value in one column

Re: Generating a count number for records with the same value in one column

Re: Generating a count number for records with the same value in one column