Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Sai33
Partner
Partner

Min and Max based on distinct fileds

Hello All,

 

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

IDLOCFrom to
1A10.10.201912.12.2019
1A13.12.201921.01.2020
1A22.01.202016.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

IDLOCFrom To
1A10.10.201916.04.2020

 

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

 

Regards

Sai

Labels (2)
1 Solution

Accepted Solutions
Mauritz_SA
Partner
Partner

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

View solution in original post

3 Replies
Mauritz_SA
Partner
Partner

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
Partner
Partner
Author

Hi Marius,

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

IDLOCFROMTO
1A10.10.201912.12.2019
1A13.12.201921.01.2020
1A22.01.202031.01.2020
1B01.02.202015.02.2020
1C16.02.202003.03.2020
1A04.03.202015.03.2020

 

Expected Solution

IDLOCFROM TO
1A10.10.201931.01.2020
1B01.02.2020 15.02.2020 
1C16.02.202003.03.2020
1A04.03.202015.03.2020

 

Mauritz_SA
Partner
Partner

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

View solution in original post