Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
chriys1337
Creator III
Creator III

Reducing Data Rows with manipulation in date fields

Hi,

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
piotrek_w
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.

Sample:
Load
PNR,
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
PNR,
Class
;
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
]
;

piotrek_w_1-1694701342776.png

 

BR,

Piotrek