topic Re: RANK in QlikView

RANK

MK_QSL — Tue, 04 Mar 2014 16:18:51 GMT

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

Re: RANK

Tue, 04 Mar 2014 16:33:41 GMT

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;

Re: RANK

Carlos_Reyes — Tue, 04 Mar 2014 16:34:33 GMT

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.

Re: RANK

MK_QSL — Tue, 04 Mar 2014 16:39:12 GMT

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

Re: Re: RANK

jerem1234 — Tue, 04 Mar 2014 17:17:43 GMT

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)

Use two expressions with dimension Part NO:

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

max(Qty)

Hope this helps!