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

[Anonymous · Accepted solution]

You can do this by using inner join instead of the left join:

Inner Join(Temp1)

Load  User,

      date(max("End Date")) as "End_Date"

Resident Temp1

Group By User;

I use date() function here because max() converts date to integer, but you want to keep the date format for the join.

[Anonymous]

Michael,

Thank you for replying. Much appreciated.

I followed your tips by doing an inner join but I am still getting multiple lines a sin the pic below:

I need 1 line per user with corresponding fields and the max_end_Date

[Anonymous]

John,

I guess from the screenshot that you used field name max_end_date, while in my example it is "End Date".  For inner join to work as intended in this case, field name must be the same in Temp1 and in the 'inner join", so all the records where the field doesn't match will be removed.

[maxgro]

Temp1:

Load User,

         Cell Phone Plan,

         Start Date,

         End Date

From excel;

Final:

noconcatenate load *

resident Temp1

where peek(User) <> User                   // only first user when user of current record <> ser of previous record

order by User, [End Date] desc;          // max end date by user

drop table temp1;

[Anonymous]

Michael, Thanks for your solution. It works and you were right. I was not using the same field name for Inner Join to work.

Massimo, I will try your soln. and update it here.

Thanks again

[Anonymous]

Massimo, I used your code and I am getting the below result: Its pulling the 1st line from the row:

here is the code I used:

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];         

drop table Temp1;

let me know if I did sth incorrect. Its very possible

Thanks Massimo

[MayilVahanan]

Hi John,

Do you need to display the maximum end date records or last call made by user?

[maxgro]

in my script there was a different order by

depending on your req you have to modify the order by

order by User, [End Date] desc;  

[Anonymous]

Massimo, desc is a Qlik function or did you mean to say the rest of the description i.e. remaining fields.. cell phone plan and start date?