Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist
Specialist

How to pull max Date line?

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

qlik-snap.PNG

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

1 Solution

Accepted Solutions
Highlighted
Champion III
Champion III

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.

View solution in original post

12 Replies
Highlighted
Champion III
Champion III

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.

View solution in original post

Highlighted
Specialist
Specialist

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:

max_end_date2.PNG

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

Highlighted
Champion III
Champion III

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.

Highlighted
MVP
MVP

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;


Highlighted
Specialist
Specialist

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

Highlighted
Specialist
Specialist

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

massimo_soln.PNG

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

Highlighted

Hi John,

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

Thanks & Regards,
Mayil Vahanan R
Highlighted
MVP
MVP

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;  

Highlighted
Specialist
Specialist

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?