Skip to main content
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
Showing results for 
Search instead for 
Did you mean: 
Creator III
Creator III

Reducing Data Rows with manipulation in date fields


I have a table with a Personal Number, a  start and end date and a Class. I would like to manipulate the information in the script.

I want to reduce the rows per Personal Number. If there is NO change both rows can be combined, but the dates need an adjustment, if there is a change between both rows, both rows should be loaded.

I see following 2/3 scenarios:

  AS-IS:         Future-State:  
PNR Start End Class   PNR Start End Class
5001 01.01.2021 31.03.2021 A   5001 01.01.2021 30.12.2022 A
5001 01.04.2021 30.12.2022 A          


No change in class, so both rows can be combined; start from 1st row and end from 2nd row


  AS-IS:         Future-State:  
PNR Start End Class   PNR Start End Class
6002 01.01.2021 31.03.2021 A   6002 01.01.2021 31.03.2021 A
6002 01.04.2021 30.12.2022 B   6002 01.04.2021 30.12.2022 B


There is a change in the Class Field, so both rows should be loaded as they are. No adjustment.


  AS-iS:         Future-State:  
PNR Start End Class   PNR Start End Class
7003 01.01.2021 31.03.2021 A   7003 01.01.2021 30.12.2022 A
7003 01.04.2021 30.12.2022 A   7003 01.01.2023 15.06.2023 C
7003 01.01.2023 15.06.2023 C          


No change in the Class Field in second row, compared to first, so both rows can be combined. Comparing the new 1st row with the 3rd row there is a change in Class, so 3rd row should be loaded as 2nd row.


I would be very grateful for any help!

Thanks a lot!


Labels (1)
1 Reply
Partner - Contributor III
Partner - Contributor III

Hi @chriys1337 

Please try to take min() from Start and max() from End and then Group by PNR and Class.

Date(Min(Date#(Start, 'DD.MM.YYYY')),'DD.MM.YYYY') as Start,
Date(Max(Date#(End, 'DD.MM.YYYY')),'DD.MM.YYYY') as End
Group by
Load *
Inline [PNR, Start, End, Class
7003, 01.01.2021, 31.03.2021, A
7003, 01.04.2021, 30.12.2022, A
7003, 01.01.2023, 15.06.2023, C