MaxDate in script

[brunopaulo]

Hi Community,

I have this problem

Field1         |     Field 2     |     Field3     | Date

1                         1                        1          28-08-2018

1                         1                         1          29-08-2018

1                         2                         2          30-08-2018

1                         2                         2          09-06-2016

2                    1                              1          09-06-2016

Expect result

Field1         |     Field 2     |     Field3     | Date

1                         1                        1          29-08-2018

1                         2                        2          30-08-2018

2                         1                         1          09-06-2016

Im trying do this

Table:

Load

     Field1,

     Field2,

     Field3,

     max(date) as date

Resident TableTEMP

Group by

      Field1,

     Field2,

     Field3

;

Drop table TableTEMP;

But because the script is line by line i belive thats the reason dat isnt working. Any ideas?

Best Regards

Bruno Paulo

[chrismarlow]

I had to do a couple of things to come up with your answer;

1) Change the date setting at the top of the script - line 7 in mine

SET DateFormat='DD-MM-YYYY';

2) I had to put a 'NoConcatenate' on the load, or it kept bolting the grouped table back on to the temp (& then deleting it);

Table:

noconcatenate

Load

     Field1,

Does either/both of those help?

Regards,

Chris.

[adityaakshaya]

Hi Bruno,

I don't see any problem with your code.

Your code will work fine if you have data in Date field is in date format.

You can change the data in date field as date.

Regards,

Akshaya

[manoranjan_d]

try this

TableTEMP:

LOAD * INLINE [

    Field1, Field2, Field3, Date

    1, 1, 1, 28-08-2018

    1, 1, 1, 29-08-2018

    1, 2, 2, 30-08-2018

    1, 2, 2, 09-06-2016

    2, 1, 1, 09-06-2016

];

NoConcatenate

Table:

Load

     Field1,

     Field2,

     Field3,

date(max(date(date#(Date,'DD-MM-YYYY'))),'DD-MM-YYYY') as Date

Resident TableTEMP

Group by

      Field1,

     Field2,

     Field3

;

Drop table TableTEMP;

[CarlosAMonroy]

Hi Bruno,

It should work the way you're doing it. Getting the expected result:

TableTmp:

LOAD * INLINE [

    F1, F2, F3, Date

    1, 1, 1, 28-08-2018

    1, 1, 1, 29-08-2018

    1, 2, 2, 30-08-2018

    1, 2, 2, 09-06-2018

    2, 1, 1, 09-06-2018

];

NoConcatenate

Table:

LOAD Distinct F1, F2, F3, Date(Max(Date#(Date,'DD-MM-YYYY'))) as Date Resident TableTmp

Group By F1, F2, F3

;

DROP Table TableTmp;

Hope that helps,

Carlos M