topic Re: Adjust field in script to contain only max value in QlikView

Adjust field in script to contain only max value

Fri, 07 Aug 2015 13:30:27 GMT

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!

Re: Adjust field in script to contain only max value

maxgro — Fri, 07 Aug 2015 14:02:11 GMT

did you try?

FirstSortedValue(Industry, -Revenue)

Re: Adjust field in script to contain only max value

maxgro — Fri, 07 Aug 2015 14:04:51 GMT

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;

Re: Adjust field in script to contain only max value

Gysbert_Wassenaar — Fri, 07 Aug 2015 14:06:04 GMT

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

];

Re: Adjust field in script to contain only max value

Fri, 07 Aug 2015 15:57:38 GMT

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?

Re: Adjust field in script to contain only max value

Gysbert_Wassenaar — Fri, 07 Aug 2015 16:10:49 GMT

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.

Re: Adjust field in script to contain only max value

Fri, 07 Aug 2015 16:14:53 GMT

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

Re: Adjust field in script to contain only max value

Gysbert_Wassenaar — Fri, 07 Aug 2015 16:18:19 GMT

Can you post a qlikview document that demonstrates the problem?