Max date

[#1]

Hi,

I have data as below

Emp     Date                    Sal     Grade

ABC     01/27/2012          100     A

ABC     02/27/2012          200     B

DEF     3/31/2012            100     B

DEF      5/31/2012          400     C

My requirement is I have to pick Grades and salaries of only latest date for each employee and show it in pie chart...

so ideally I should only pick salaries and grades of employee of ABC Dated 2/27/12  and 5/31/2012 for DEF

currently Im using below formula in pie chart..

sum(salary)/sum(total slalary) so this forumla picks salaries for all records..I want this to pick only salaries and grades of recent dates for each emp and do the calcuation

any help is highly appreciated..

[rlp]

Use the following code:

max_table:

NoConcatenate

LOAD

     Emp ,

     Max( Date ) as Date

RESIDENT <your_table>

GROUP BY Emp

;

LEFT JOIN( max_table )

LOAD

     *

RESIDENT <your_table> ;

DROP TABLE <your_table> ;