Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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 |
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