Skip to main content
Announcements
YOUR OPINION MATTERS! Please take the Qlik Experience survey you received via email. Survey ends June 14.
cancel
Showing results for 
Search instead for 
Did you mean: 
draghici1109
Creator
Creator

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

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

 

Alexandru Draghici
BICC at Komm.ONE
Labels (2)
1 Solution

Accepted Solutions
MarcoWedel

Hi,

one solution could be:

MarcoWedel_0-1642967288486.png

 

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

View solution in original post

3 Replies
MarcoWedel

Hi,

one solution could be:

MarcoWedel_0-1642967288486.png

 

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

draghici1109
Creator
Creator
Author

Thanks Marco!

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

Kind Regards and Kudos!

Alexandru

Alexandru Draghici
BICC at Komm.ONE
MarcoWedel

you're welcome

please close this thread by accepting a solution

thanks