Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

hencovanee
Contributor II

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

Tags (3)
1 Solution

Accepted Solutions
Not applicable

Re: Min / max function

LOAD ACCOUNTID,

     max(date(DATESCHEDULED)) as DATESCHEDULED

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

left join load CONTACTID,

     ACCOUNTID,

     DATESCHEDULED

FROM ......;

10 Replies
hencovanee
Contributor II

Re: Min / max function

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.

MVP
MVP

Re: Min / max function

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

Re: Min / max function

AGGRTABLE:

LOAD CONTACTID,

MAX(ACCOUNTID) AS ACCOUNTID,

MAX(DATESCHEDULED) AS DATESCHEDULED;

RESIDENT MAINDATA

GROUP BY CONTACTID;

Not applicable

Re: Min / max function

LOAD ACCOUNTID,

     max(date(DATESCHEDULED)) as DATESCHEDULED

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

left join load CONTACTID,

     ACCOUNTID,

     DATESCHEDULED

FROM ......;

nstefaniuk
Contributor III

Re: Min / max function

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.

hencovanee
Contributor II

Re: Min / max function

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

Re: Min / max function

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

hencovanee
Contributor II

Re: Min / max function

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

Re: Min / max function

Yes