Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
vengadeshpalani
Creator
Creator

Remove min date using group by

Hi All

I have table like

JobIDDateReasonCode
J105/05/2015A
J105/06/2015B
J105/09/2015D
J206/01/2017A
J205/25/2015B
J307/20/2016D
J306/15/2015E
J306/05/2016H
J405/08/2016J

I want to remove the min date row for each JobID in script level

so the result should be like

JobIDDateReasonCode
J105/06/2015B
J105/09/2015D
J206/01/2017A
J307/20/2016D
J306/05/2016H
1 Solution

Accepted Solutions
swuehl
MVP
MVP

Maybe like this:

INPUT:

LOAD JobID,

     Date,

     ReasonCode

FROM

[https://community.qlik.com/thread/259614]

(html, codepage is 1252, embedded labels, table is @1);

Result:

NoConcatenate

LOAD *

RESIDENT INPUT

WHERE JobID =Previous(JobID)

ORDER BY JobID, Date asc;

DROP TABLE INPUT;

View solution in original post

4 Replies
swuehl
MVP
MVP

Maybe like this:

INPUT:

LOAD JobID,

     Date,

     ReasonCode

FROM

[https://community.qlik.com/thread/259614]

(html, codepage is 1252, embedded labels, table is @1);

Result:

NoConcatenate

LOAD *

RESIDENT INPUT

WHERE JobID =Previous(JobID)

ORDER BY JobID, Date asc;

DROP TABLE INPUT;

Anil_Babu_Samineni

May be this?

Capture.PNG

Directory:

LOAD JobID,

     Date,

     ReasonCode

FROM

[https://community.qlik.com/message/1263493]

(html, codepage is 1252, embedded labels, table is @1) ;

Final:

NoConcatenate

LOAD *

Resident Directory

Where JobID =Previous(JobID)

Order By JobID;

Drop Table Directory;

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
MindaugasBacius
Partner - Specialist III
Partner - Specialist III

tmp:

LOAD JobID,

     Date,

     ReasonCode

FROM

[https://community.qlik.com/thread/259614]

(html, codepage is 1257, embedded labels, table is @1);

NoConcatenate

tmp1:

LOAD *

  ,AutoNumber(rowno(), JobID) as Nr

Resident tmp

Order By JobID asc, Date asc;

DROP Table tmp;

NoConcatenate

tmp:

LOAD JobID,

     Date,

     ReasonCode

Resident tmp1

Where Nr <> 1

;

DROP Table tmp1;

Result:

Screenshot_1.jpg

Also, as I do understand you do not need the last row?

JobIDDateReasonCode
J105/06/2015B
J105/09/2015D
J206/01/2017A
J307/20/2016D
J306/05/2016H
Anonymous
Not applicable

Hi,

This script give you required output, Hope this will help you!!

tmp:

LOAD * Inline [

JobID ,Date ,ReasonCode

J1 ,05/05/2015, A

J2 ,06/01/2017 ,A

J1 ,05/06/2015, B

J2 ,05/25/2015, B

J1 ,05/09/2015, D

J3 ,07/20/2016 ,D

J3 ,06/15/2015, E

J3 ,06/05/2016, H

J4 ,05/08/2016, J

];

Left Join

tab2:

LOAD  JobID,

Date(min(Date),'MM/DD/YYYY')  as MinDate

Resident tmp Group by JobID ;

tab3:

LOAD JobID, Date ,ReasonCode Resident tmp WHERE not EXISTS (MinDate,Date) ;

DROP Table tmp;

Thanks

Paridhi