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

[dmohanty]

Hi All,

Any help here please

[Anonymous]

Hi DMohanty,

I think you need below result:

IDs	Create Date	Deliver Date
100-10	2/16	2/18
100-20	3/23	3/27

because you say that you want min of Deliver Date.

Please find solution attached. Hopefully it should work.

Cheers,

Ritesh Pathak

[Anonymous]

Hi DMohanty,

Has it worked?

[dmohanty]

Hi Ritesh,

Thank you for help.

But exactly this is not my solution. I need like this.

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

Because of the Create Date = 2/16, Min Delivery Date would be 02/19 (not 02/18)

and for Create Date = 2/23, Min Delivery Date would be 02/28 (not 02/27)

Any more help please

[Anonymous]

What do you mean by Min Delivery Date? I understood 'Minimum', do you mean something else e.g. Mid date or something?

Please explain. That would help me in understanding your problem better.

[dmohanty]

Not Mid Date.

I mean,

      the corresponding Min Delivery Date of the Max Create Date.

If you look into the source, for 100-10, the Max Create Date is 2/16. And for 2/16, the corresponding Min Delivery Date is 2/19. (not 2/18)

Similarly for 100-20. the Max Create Date is 3/23 and there is single Delivery Date for that corresponding Create Date i.e 3/28. So this treated as Min for this ID.

Hope this is clear.

[Anonymous · Accepted solution]

Hi DMohanty,

I got your problem now. Here is your solution mate. Please find attached files.

Please let me know whether it worked or not.

Thanks,

Ritesh Pathak

[dmohanty]

Hey Ritesh,

Many thanks for the efforts. Surely have the results that was needed.

Le me know implement at my full data set and will let know.

Regards!

[Anonymous]

Cheers mate. If it works. Please mark this solution as 'Correct Answer' so that thread could be closed.

Regards,

Ritesh Pathak