Find Maximum Value

[danielle_v]

Hi All,

In my data there is a field called Container. Each container will have one or more Groups against it, and I am trying to create a new field in the script (I will call this Group2) which gives me the name of the Group with the greatest number of units, by container.

The below table gives an example of how my data looks;

Container	Group	Units
1	Clothing	10,000
2	Home	500
2	Clothing	1,000
3	Accessories	1,000
3	Home	20,000
3	General	100
4	Accessories	5,000
4	Clothing	10,000

And this is what I would want the Group2 field to show, based on the above examples;

1	Clothing
2	Clothing
3	Home
4	Clothing

So where there is more than one Group per Container, I need the Group2 field to show the Group with the greatest number of Units.

I've tried various things using group by, firstsortedvalue and aggr, but can't get this to work.

Any suggestions greatly appreciated!

[giakoum · Accepted solution]

firstsortedvalue is the function you need to use :

load

...

firstsortedvalue(Group, Units) as FieldName

....

group by Container

Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL. By stating an n larger than 1, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.

Example:

Load Customer,

firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.csv

group by Customer;

[danielle_v]

Hi Ioannis,

Thanks for your response - this is very helpful and confirms that I am going down the right route!

It's still not working totally as it should be, and I think that's because I've missed out a step in the calculation. There may be multiple records per Group in each Container, so I think I first need to sum up the Units by Group and Container before doing the fisrtsortedvalue calculation.

Thanks again!

[giakoum]

Please close this discussion

[MarcoWedel]

Hi,

one small correction, because you have to use the negative Units value as second parameter in the FirstSortedValue function to get the Group having the maximum Units value:

table1:

LOAD * FROM [https://community.qlik.com/thread/164553] (html, codepage is 1252, embedded labels, table is @1);

table2:

LOAD Container,

    FirstSortedValue(Group, -Units) as Group2

Resident table1

Group By Container;

hope this helps

regards

Marco

[danielle_v]

Hi Marco - thanks for confirming!

I had actually added in the negative, but forgot to mention in my earlier response 🙂