topic Re: Min / max function in QlikView

Min / max function

Anonymous — Tue, 24 Jul 2012 12:35:40 GMT

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:

CONTACTID	ACCOUNTID	DATESCHEDULED
10	150	02-05-2012
12	150	06-07-2012
15	150	05-06-2012
20	150	03-04-2012
50	160	05-02-2012
55	160	05-07-2012
65	160	16-04-2012

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

CONTACTID	ACCOUNTID	DATESCHEDULED
12	150	06-07-2012
55	160	05-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

Re: Min / max function

Anonymous — Tue, 24 Jul 2012 12:43:08 GMT

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.

Re: Min / max function

jonathandienst — Tue, 24 Jul 2012 12:45:23 GMT

Have a look in the manual at FirstSortedValue()

Regards

Jonathan

Re: Min / max function

Tue, 24 Jul 2012 12:46:56 GMT

AGGRTABLE:

LOAD CONTACTID,

MAX(ACCOUNTID) AS ACCOUNTID,

MAX(DATESCHEDULED) AS DATESCHEDULED;

RESIDENT MAINDATA

GROUP BY CONTACTID;

Re: Min / max function

Tue, 24 Jul 2012 12:48:35 GMT

LOAD ACCOUNTID,

max(date(DATESCHEDULED)) as DATESCHEDULED

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

left join load CONTACTID,

ACCOUNTID,

DATESCHEDULED

FROM ......;

Re: Min / max function

nstefaniuk — Tue, 24 Jul 2012 12:55:08 GMT

In Oracle:

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.

Re: Min / max function

Anonymous — Tue, 24 Jul 2012 13:01:00 GMT

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

Re: Min / max function

nstefaniuk — Tue, 24 Jul 2012 13:07:03 GMT

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

Re: Min / max function

Anonymous — Tue, 24 Jul 2012 13:09:35 GMT

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

Re: Min / max function

nstefaniuk — Tue, 24 Jul 2012 13:10:15 GMT

Yes

Re: Min / max function

Anonymous — Tue, 24 Jul 2012 13:10:40 GMT

Thanks.