Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Min / max function

Hi all,

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:

CONTACTIDACCOUNTIDDATESCHEDULED
1015002-05-2012
1215006-07-2012
1515005-06-2012
2015003-04-2012
5016005-02-2012
5516005-07-2012
6516016-04-2012

Now I would like to see the contactids per accountid which have the highest date. The result should be like this:

CONTACTIDACCOUNTIDDATESCHEDULED
1215006-07-2012
5516005-07-2012

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.

Regards,

Henco

1 Solution

Accepted Solutions
Not applicable
Author

LOAD ACCOUNTID,

     max(date(DATESCHEDULED)) as DATESCHEDULED

FROM .......group by ACCOUNTID;

left join load CONTACTID,

     ACCOUNTID,

     DATESCHEDULED

FROM ......;

View solution in original post

10 Replies
Anonymous
Not applicable
Author

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.

jonathandienst
Partner - Champion III
Partner - Champion III

Hi

Have a look in the manual at FirstSortedValue()

Regards

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

AGGRTABLE:

LOAD CONTACTID,

MAX(ACCOUNTID) AS ACCOUNTID,

MAX(DATESCHEDULED) AS DATESCHEDULED;

RESIDENT MAINDATA

GROUP BY CONTACTID;

Not applicable
Author

LOAD ACCOUNTID,

     max(date(DATESCHEDULED)) as DATESCHEDULED

FROM .......group by ACCOUNTID;

left join load CONTACTID,

     ACCOUNTID,

     DATESCHEDULED

FROM ......;

nstefaniuk
Creator III
Creator III

In Oracle:

select ACCOUNTID, min(CONTACTID) keep (dense_rank first order by DATESCHEDULED desc) FROM MYTABLE GROUP BY ACCOUNTID ORDER BY ACCOUNTID;

or

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.

Anonymous
Not applicable
Author

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.

Thanks again.

Cheers,

Henco

nstefaniuk
Creator III
Creator III

Take care that if you have 2 CONTACTID on same date, you will have duplicates...

Anonymous
Not applicable
Author

nstefaniuk, thanks. I assume that's only the case for 2 CONTACTID's on the same date for one specific customer, isn't it?

nstefaniuk
Creator III
Creator III

Yes