Max Group By 2 fields

[#1]

Hi,

I try to use the max() function with a Group By and 2 values.

My DATA are looking like this :

Quarter	Type_of_Value	Value	Country
Q1	A	230	UK
Q1	A	200	US
Q1	A	250	FR
Q1	B	120	UK
Q1	B	130	US
Q1	B	100	FR
Q2	A	300	IT
Q2	A	250	US
Q2	B	100	FR
Q2	B	110	UK
Q2	..	...	...

I need to achieve the following table :

Quarter	MaxTypeofValue	MaxCountryValue	MaxCountry
Q1	A	250	FR
Q1	B	130	US
Q2	A	300	IT
Q2	B	110	UK
...

I have tried with the following script :

DATA_MAX:

LOAD

max(Value) as MaxCountryValue,

Type_of_Value as Max_Type_of_Value,

Quarter

FROM

DATA_SOURCE.qvd

(qvd)

Group by Quarter,Type_of_Value;

Left Join

LOAD Country as MaxCountry,

     Value as MaxCountryValue

FROM

DATA_SOURCE.qvd

(qvd);

But I obtain this wrong result :

Quarter	MaxTypeofValue	MaxCountryValue	MaxCountry
Q2	A	300	IT
Q1	B	130	US

All idea or remark is welcome !

Thanks a lot !

Benoit

[#2]

Have you tried reversing the group by? Not sure if it will work but worth a try:

Group by Type_of_Value,Quarter;

You could also try a left keep?

[#3]

I have this script and the results come out fine:

Table1:

LOAD * INLINE [

Quarter, Type_Of_Value, Value, Country

Q1, A, 230, UK

Q1, A, 200, US

Q1, A, 250, FR

Q1, B, 120, UK

Q1, B, 130, US

Q1, B, 100, FR

Q2, A, 300, IT

Q2, A, 250, US

Q2, B, 100, FR

Q2, B, 110, UK

];

Table2:

LOAD Quarter,

MAX(Value) as MaxCountryValue,

Type_Of_Value as MaxTypeOfValue

FROM

C:\Users\BApperson\Desktop\Table1.qvd

(qvd)

GROUP BY Quarter, Type_Of_Value;

LEFT JOIN

LOAD

Quarter,

Value as MaxCountryValue,

Country as MaxCountry

FROM

C:\Users\BApperson\Desktop\Table1.qvd

(qvd);

drop table Table1;

Part of what might be a problem is what you are joining on, notice in the second statement I have Quarter and MaxCountryValue that are being joined on.

Hope this helps.

-Brandon

[#4]

I tried your two solution ... finally I find why my solution was wrong : my table wasn't sorted ...

Thanks a lot for your help !

[#5]

Well,

In fact, my goal is to obtain a table and a list of selection on the quarter. If I choose Quarter Q1 it should display this table :

	COUNTRY
Type A	FR
	250
Type B	US
	130

Has anyone an idea to obtain this result without using Firstsortedvalue in the table (it takes too many time to display dynamically).

Thanks again for your help !

[#6 · Accepted solution]

Hey, I'm not sure what do you mean by "without using FirstsortedValue".  Try the below script. Should work for your case. It uses an aggregation function. 

Table1:

LOAD * INLINE [

Quarter, Type_Of_Value, Value, Country

Q1, A, 230, UK

Q1, A, 200, US

Q1, A, 250, FR

Q1, B, 120, UK

Q1, B, 130, US

Q1, B, 100, FR

Q2, A, 300, IT

Q2, A, 250, US

Q2, B, 100, FR

Q2, B, 110, UK

];

load
Quarter,
Type_Of_Value,
max(Value),
lastValue(Country)
resident Table1 group by Quarter,Type_Of_Value order by Value;

drop table Table1;

Regards,

Xue Bin

[#7]

Thx Xue Bin !

It's working perfectly !

Regards,

Benoît