RANK

[MK_QSL]

Hi All,

I have below table and want TOP CUSTOMER for EACH Part NO...

SALES:

LOAD * Inline

[

  Part NO, Customer, Qty

  A, C1, 10

  A, C2, 14

  A, C3, 12

  B, C1, 14

  B, C2, 18

  B, C3, 18

  C, C1, 16

  C, C2, 16

  C, C3, 16

];

The final table I want is

Part NO     TOP Customer       QTY

A               C2                         14

B               C2, C3                   18

C               C1, C2, C3             16

[#2]

SALES:

LOAD * Inline

[

  PartNO, Customer, Qty

  A, C1, 10

  A, C2, 14

  A, C3, 12

  B, C1, 14

  B, C2, 18

  B, C3, 18

  C, C1, 16

  C, C2, 16

  C, C3, 16

];

left join

load PartNO,max(Qty) as maxqty resident Test

group by PartNO;

NewSales:

laod PartNO,Qty,concat(Customer,',') resident SALES

where maxqty = Qty

Grooup by PartNO,Qty;

drop table SALES;

[Carlos_Reyes]

You can use this after you INLINE LOAD:

LEFT JOIN
LOAD
  [Part NO],
  Max(Qty) as MaxQty
Resident SALES
Group By [Part NO]
;
LEFT JOIN
LOAD
  [Part NO],
  Qty as MaxQty,
  Customer as [Max Customer]
Resident SALES
;

Then, create a Tablebox using [Part No], [Max Customer] and MaxQty fields to get your result.

[MK_QSL]

Thanks for your reply but I can't do this in Script side.

[jerem1234 · Accepted solution]

Please see if attached is what you are looking for, used as dimension with Part NO:

aggr(concat(if(aggr(rank(sum(Qty),1), [Part NO], Customer)=1,Customer), ', '), [Part NO])

With Expression:

max(Qty)

OR

Use two expressions with dimension Part NO:

concat(if(aggr(rank(sum(Qty),1), [Part NO], Customer)=1,Customer), ', ')

max(Qty)

Hope this helps!