Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
vengadeshpalani
Contributor

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
MVP
MVP

Re: Remove min date using group by

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;

4 Replies
MVP
MVP

Re: Remove min date using group by

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;

Re: Remove min date using group by

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)
MindaugasBacius
Valued Contributor III

Re: Remove min date using group by

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
paridhimantri
Contributor

Re: Remove min date using group by

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