Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
desc? QlikView for sort in descending order
like desc in SQL
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;
For Users looking for correct soln, this is also the right answer to the question.