topic Re: How to pull max Date line? in QlikView

How to pull max Date line?

Anonymous — Mon, 18 May 2015 19:08:35 GMT

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

Re: How to pull max Date line?

Anonymous — Mon, 18 May 2015 20:00:25 GMT

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.

Re: How to pull max Date line?

Anonymous — Mon, 18 May 2015 20:32:10 GMT

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

Re: How to pull max Date line?

Anonymous — Mon, 18 May 2015 20:52:46 GMT

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.

Re: How to pull max Date line?

maxgro — Mon, 18 May 2015 21:55:54 GMT

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;

Re: How to pull max Date line?

Anonymous — Tue, 19 May 2015 04:54:36 GMT

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

Re: How to pull max Date line?

Anonymous — Wed, 20 May 2015 01:59:12 GMT

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

Re: How to pull max Date line?

MayilVahanan — Wed, 20 May 2015 04:42:51 GMT

Hi John,

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

Re: How to pull max Date line?

maxgro — Wed, 20 May 2015 07:35:47 GMT

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;

Re: How to pull max Date line?

Anonymous — Wed, 20 May 2015 16:19:38 GMT

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?

Re: How to pull max Date line?

maxgro — Wed, 20 May 2015 16:23:56 GMT

desc? QlikView for sort in descending order

like desc in SQL

Re: How to pull max Date line?

Anonymous — Wed, 20 May 2015 16:48:08 GMT

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;

Re: How to pull max Date line?

Anonymous — Wed, 20 May 2015 16:49:42 GMT

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