Reducing Data Rows with manipulation in date fields

chriys1337 — Thu, 14 Sep 2023 08:28:49 GMT

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!

Re: Reducing Data Rows with manipulation in date fields

piotrek_w — Thu, 14 Sep 2023 14:23:35 GMT

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
]
;

BR,

Piotrek

topic Reducing Data Rows with manipulation in date fields in App Development

Reducing Data Rows with manipulation in date fields

Re: Reducing Data Rows with manipulation in date fields