Discussion Board for collaboration on QlikView Scripting.
I think this is a question which can be solved with a min/max function, but I can't get it done. I hope one of you guys can help me out.
I do have the following data:
Now I would like to see the contactids per accountid which have the highest date. The result should be like this:
As you can see, there is no logic between the contactids and the datescheduled.
I am using Oracle SQL, but solutions in another way are also welcome.
Solved! Go to Solution.
EDIT: of course I can get this result in QV itself by using max(datescheduled) as expression and accountid as dimension, but I would like to do this in the script. Hope one of you can help me out.
select ACCOUNTID, min(CONTACTID) keep (dense_rank first order by DATESCHEDULED desc) FROM MYTABLE GROUP BY ACCOUNTID ORDER BY ACCOUNTID;
select ACCOUNTID, min(CONTACTID) keep (dense_rank last order by DATESCHEDULED asc) FROM MYTABLE GROUP BY ACCOUNTID ORDER BY ACCOUNTID;
the min(CONTACTID) is used if you have 2 CONTACTID on the same ACCOUNTID with the same highest DATESCHEDULED.
Everyone who reacted here, many thanks. It works fine! As I can see there are many ways to accomplish this, I choose the way Pari Pari told me to do it.