Max and Min of Dates

[dmohanty]

Hi All,

I have a data sheet like this below.

Need to show the unique IDs, where the Create Date is Max for that IDs and if there are multiple Deliver Date for that Create Date, show the Min of Deliver Date

How to write the Load Script?

SOURCE:

IDs	Create Date	Deliver Date
100-10	2/15	2/18
100-10	2/16	2/19
100-10	2/16	2/20
100-20	3/22	3/27
100-20	3/23	3/28

RESULT:

IDs	Create Date	Deliver Date
100-10	2/16	2/19
100-20	3/23	3/28

[jagan]

Hi,

Try this script in a much smarter way

Temp:

LOAD

IDs,

CreateDate, DeliverDate,

MakeDate(Year(Today()), SubField([CreateDate], '/', 1), SubField(CreateDate, '/', 2)) AS CreateDate_F,

MakeDate(Year(Today()), SubField([DeliverDate], '/', 1), SubField(DeliverDate, '/', 2)) AS DeliverDate_F

INLINE [

IDs, CreateDate, DeliverDate

100-10, 2/15, 2/18

100-10, 2/16, 2/19

100-10, 2/16, 2/20

100-20, 3/22, 3/27

100-20, 3/23, 3/28];

Data:

NoConcatenate

LOAD

IDs, CreateDate, DeliverDate, Flag

WHERE Flag = 1;

LOAD

*,

If(Peek(IDs) <> IDs, 1, 0) AS Flag

RESIDENT Temp

ORDER BY IDs, CreateDate_F Desc, DeliverDate_F;

DROP TABLE Temp;

Exit Script;

Regards,

Jagan.

[dmohanty]

Hey Jagan,

Thank you very much.... This helped a lot as well....and saved reload time too!

Cheers!