QlikView table: How do i show the main industrie in every line

[georgwildmoser]

Hello,

maybe the question has already been asked, but I didn't find the post. My requirement is the following:

I have created a table with sales information about the customer and I want to expand it. The extension should give me the main industry of each customer in every line.

This is what the current table looks like:

This should show the table in the future:

The customer's main industry is the one with the highest turnover of the customer. This should be determined using a formula. I've already tried aggr and rank, but I don't get the information for every line by customer. 

I'm stuck here and would be happy to get some help.

Thanks in advance.

[Saravanan_Desingh · Accepted solution]

One solution is.

tab1:
LOAD * INLINE [
    customer, industries, revenue
    Cust A, Aero, 40
    Cust A, Elec, 10
    Cust A, Mech, 20
    Cust A, Process, 30
    Cust B, Elec, 11
    Cust B, Mech, 22
    Cust B, Process, 33
];

Left Join (tab1)
LOAD customer, FirstSortedValue(industries, -revenue) As [main customers industry]
Resident tab1
Group By customer
;