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: 
Not applicable

Max Group By 2 fields

Hi,

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

My DATA are looking like this :

QuarterType_of_ValueValueCountry
Q1A230UK
Q1A200US
Q1A250FR
Q1B120UK
Q1B130US
Q1B100FR
Q2A300IT
Q2A250US
Q2B100FR
Q2B110UK
Q2........

I need to achieve the following table :

Quarter MaxTypeofValueMaxCountryValueMaxCountry
Q1A250FR
Q1B130US
Q2A300IT
Q2B110UK
...


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 :

QuarterMaxTypeofValueMaxCountryValueMaxCountry
Q2A300IT
Q1B130US

All idea or remark is welcome !

Thanks a lot !

Benoit

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

6 Replies
Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

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

Thanks a lot for your help !

Not applicable
Author

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 AFR

250
Type BUS

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 !

Not applicable
Author

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

Not applicable
Author

Thx Xue Bin !

It's working perfectly !

Regards,

Benoît