How to pull max Date line?

[Anonymous]

For each user I want to fetch the line with max end date only.

For example, The table I want to create should have :A, 2nd, Start Date 4/1/2012, End Date 3/30/2012. How do I get that result.

I have the following script:

Temp1:

Load User,

         Cell Phone Plan,

         Start Date,

         End Date

From excel;

Left Join(Temp1)

Temp2:

Load  User,

      max(Date(End Date) as Max_End_Date

Resident Temp1

Group By User;

This will add the max date on Temp1 but Now my challenge is how do I load only those lines in Table Temp1 that has a max end date? I tried creating a new table temp3 and did a resident load where End Date = Max_End_Date and then Drop Temp1. but It does not work. I know I can join the 3rd table to another 4th and drop Temp1table but I do not want to do that. How do I achieve this in one table without having to Join on 3rd or fourth table?

Thanks much

[maxgro]

desc? QlikView for sort in descending order

like desc in SQL

[Anonymous]

Massimo,

you are correct your soln works perfectly well too. I have posted the script below. Thanks a lot.

Temp1:

LOAD User,

     [Cell Phone Plan],

     [Start Date],

     [End Date]

FROM  Excel;

Final:

noconcatenate

Load *

resident Temp1

where peek(User) <> User                 

order by User, [End Date] desc;         

drop table Temp1;

[Anonymous]

For Users looking for correct soln, this is also the right answer to the question.