topic Re: Remove min date using group by in QlikView

Remove min date using group by

vengadeshpalani — Wed, 25 Nov 2020 16:16:04 GMT

Hi All

I have table like

JobID	Date	ReasonCode
J1	05/05/2015	A
J1	05/06/2015	B
J1	05/09/2015	D
J2	06/01/2017	A
J2	05/25/2015	B
J3	07/20/2016	D
J3	06/15/2015	E
J3	06/05/2016	H
J4	05/08/2016	J

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

so the result should be like

JobID	Date	ReasonCode
J1	05/06/2015	B
J1	05/09/2015	D
J2	06/01/2017	A
J3	07/20/2016	D
J3	06/05/2016	H

Re: Remove min date using group by

swuehl — Mon, 08 May 2017 09:10:01 GMT

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

Anil_Babu_Samineni — Mon, 08 May 2017 09:13:35 GMT

May be this?

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;

Re: Remove min date using group by

MindaugasBacius — Mon, 08 May 2017 09:17:26 GMT

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:

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

JobID	Date	ReasonCode
J1	05/06/2015	B
J1	05/09/2015	D
J2	06/01/2017	A
J3	07/20/2016	D
J3	06/05/2016	H

Re: Remove min date using group by

Anonymous — Mon, 08 May 2017 11:22:00 GMT

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