Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
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

Highlighted

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;

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Specialist III
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
Highlighted
Creator
Creator

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