max date

[Anonymous]

HI,

I've   a table like this:

Date               Amount               OTHER FIELDS

2013-09-12          555

2013-09-18          666

2013-10-12          11111                    ccxx

2013-10-28          12131                    xxx

2013-11-07         1111

2013-11-15          33333

2013-11-27               4444

I would have a table with the max date for each month, like this

2013-09-18     -.....

2013-10-28     .......

2013-11-27

How can I solve it ?

Help please

Thanks

[Carlos_Reyes]

I would do it in the script:

Table1:

LOAD

          Date,

          Amount,

          Field1,

          Field2

          FieldN,

MonthName(Date) AS MonthYear,

FROM YOURSOURCE;

Table2:

LOAD

          MonthYear,

          MAX(Date)     AS     LastDateofMonth

RESIDENT Table1

GROUP BY MonthYear;

[jagan · Accepted solution]

Table1:

LOAD

          Date,

          Amount,

         '

          '

FROM Source1;

INNER JOIN (Table1)

LOAD

          MonthYear,

          Date(MAX(Date), 'YYYY-MM-DD')  AS    Date

RESIDENT Table1

GROUP BY MonthYear;

Now your table has only Max date rows of every month.

Regards,

Jagan.

[Anonymous]

Thanks to everybody !!

[#5]

hello  even i have  same  problem  can  you please  help me  out  

cid     cdate                       ctime     phone

101   10-dec-2013        10.15                   1

101   11-dec-2013         5.15              2

102    10-dec-2013        3.15             3

102    10-dec-2013       4.15              4  

103    10-dec-2013         6.15            5

103    10-dec-2013         7.15           6

103    11-dec-2013         8.15           7

i  need  for   max(date  )   in that max(date)    i need  max( time )    for  coustmer  101,102  ,103   in script level please help me  out

[#6]

Hi Bibopipo,

Try this code ,

Dev01:

Load * Inline

[

Date,Amount,OTHER_FIELDS

2013-09-12,555

2013-09-18,666

2013-10-12,11111

2013-10-28,12131

2013-11-07,1111

2013-11-15,33333

2013-11-27,4444

];

LOAD max(Date(Date,'MM-DD-YYYY')) as Max_Date Resident Dev01

Group by Date;

[#7]

For Max of Date & Max of time seperately, you can use FirstValue Function. Order the Data set on Date & Time (Both decending) & use FirstValue(Date) & FirstValue(Time).

This should do the trick.