Min and Max based on distinct fileds

[Sai33]

Hello All,

I'm trying to get the following logic in Script load. My data looks like the below table 

ID	LOC	From	to
1	A	10.10.2019	12.12.2019
1	A	13.12.2019	21.01.2020
1	A	22.01.2020	16.04.2020

I'm looking to make a Min and Max for From and To respectively and have the data as in the following table

ID	LOC	From	To
1	A	10.10.2019	16.04.2020

As mentioned i would want to do this at the Script level

Regards

Sai

[Mauritz_SA]

Hi @Sai33 

This should work:

Original:
Load
*
Inline
[
ID,LOC,From ,To
1,A,10.10.2019,12.12.2019
1,A,13.12.2019,21.01.2020
1,A,22.01.2020,16.04.2020
];

NoConcatenate

Final:
Load
ID,
LOC,
Date(Min(Date#(From,'DD.MM.YYYY')),'DD.MM.YYYY') AS From,
Date(Max(Date#(To,'DD.MM.YYYY')),'DD.MM.YYYY') AS To
RESIDENT
Original
GROUP BY ID, LOC;

DROP TABLE Original;

Regards,

Mauritz

[Sai33]

Hi Marius,

Thanks for the suggestion, it almost works fine. But, the following condition fails

ID	LOC	FROM	TO
1	A	10.10.2019	12.12.2019
1	A	13.12.2019	21.01.2020
1	A	22.01.2020	31.01.2020
1	B	01.02.2020	15.02.2020
1	C	16.02.2020	03.03.2020
1	A	04.03.2020	15.03.2020

Expected Solution

ID	LOC	FROM	TO
1	A	10.10.2019	31.01.2020
1	B	01.02.2020	15.02.2020
1	C	16.02.2020	03.03.2020
1	A	04.03.2020	15.03.2020

[Mauritz_SA · Accepted solution]

Hi @Sai33 

This should work:

Original:
Load
*,
If(LOC = Previous(LOC), Peek(Grouping),RowNo()) as Grouping
Inline
[
ID,LOC,FROM,TO
1,A,10.10.2019,12.12.2019
1,A,13.12.2019,21.01.2020
1,A,22.01.2020,31.01.2020
1,B,01.02.2020,15.02.2020
1,C,16.02.2020,03.03.2020
1,A,04.03.2020,15.03.2020
];

NoConcatenate

Final:
Load
Grouping,
ID,
LOC,
Date(Min(Date#(FROM,'DD.MM.YYYY')),'DD.MM.YYYY') AS FROM,
Date(Max(Date#(TO,'DD.MM.YYYY')),'DD.MM.YYYY') AS TO
RESIDENT
Original
GROUP BY Grouping,ID, LOC;

DROP FIELD Grouping;
DROP TABLE Original;

Regards,

Mauritz