Adjust field in script to contain only max value

[#1]

I have a table with three fields:  Customer // Industry // Revenue

Customer-to-Industry is a one-to-many mapping.  However, I want to convert it to a one-to-one mapping by only referencing the industry with the max revenue.  See tables below for illustration:

Initial table:

Customer // Industry // Revenue

A // X // 100

A // Y // 200

B // Z // 300

B // Q // 400

final table:

A // Y // 300

B // Q // 700

How can I do this?   I tried using first sorted value, but to no avail.  Thanks very much!

[maxgro · Accepted solution]

did you try?

FirstSortedValue(Industry, -Revenue)

[maxgro]

and for script

InitialTable:

load * inline [

Customer , Industry , Revenue

A , X , 100

A , Y , 200

B , Z , 300

B , Q , 400

];

Final:

noconcatenate load

Customer, sum(Revenue),

FirstSortedValue(Industry, -Revenue)

Resident InitialTable

group by Customer;

[Gysbert_Wassenaar]

Initial_table:

LOAD Customer, FirstSortedValue(Industry,-Revenue) as Industry, max(Revenue) as Revenue

Group By Customer;

LOAD * INLINE [

Customer , Industry , Revenue

A , X , 100

A , Y , 200

B , Z , 300

B , Q , 400

];

[#5]

thanks, this appears to work for most entries ... strange thing is that for some Customers, the FirstSortedValue() calc returns null, while there are records with positive revenue where Industry is not null

so Customer A has two potential values for Industry (X, Y) and both are positive revenues, however FirstSorted.Industry = null .... happens for a bunch of customers ... any idea why?

[Gysbert_Wassenaar]

Because the FirstSortedValue function can return only one value it will return null if there are several possible Industry values that all have the same maximum Revenue value.

[#7]

Thanks, but in each of these cases, the max Revenues are not equal..

[Gysbert_Wassenaar]

Can you post a qlikview document that demonstrates the problem?