Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
qlikdash
Valued Contributor

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
mov
Esteemed Contributor III

Re: How to pull max Date line?

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
mov
Esteemed Contributor III

Re: How to pull max Date line?

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

qlikdash
Valued Contributor

Re: How to pull max Date line?

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

mov
Esteemed Contributor III

Re: How to pull max Date line?

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.

MVP
MVP

Re: How to pull max Date line?

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;


qlikdash
Valued Contributor

Re: How to pull max Date line?

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

qlikdash
Valued Contributor

Re: How to pull max Date line?

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

Re: How to pull max Date line?

Hi John,

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

MVP
MVP

Re: How to pull max Date line?

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;  

qlikdash
Valued Contributor

Re: How to pull max Date line?

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?