Discussion Board for collaboration on QlikView Scripting.
I try to use the max() function with a Group By and 2 values.
My DATA are looking like this :
I need to achieve the following table :
I have tried with the following script :
max(Value) as MaxCountryValue,
Type_of_Value as Max_Type_of_Value,
Group by Quarter,Type_of_Value;
LOAD Country as MaxCountry,
Value as MaxCountryValue
But I obtain this wrong result :
All idea or remark is welcome !
Thanks a lot !
Go to 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.
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;
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?
I have this script and the results come out fine:
MAX(Value) as MaxCountryValue,
Type_Of_Value as MaxTypeOfValue
GROUP BY Quarter, Type_Of_Value;
Value as MaxCountryValue,
Country as MaxCountry
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.
I tried your two solution ... finally I find why my solution was wrong : my table wasn't sorted ...
Thanks a lot for your help !
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 :
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 !
Thx Xue Bin !
It's working perfectly !