Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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
Thanks Marco!
This is the solution. I realize, I did not understand until now the function AutoNumber.
Kind Regards and Kudos!
Alexandru
you're welcome
please close this thread by accepting a solution
thanks