topic Re: Find Min From Multiple Rows in QlikView

Find Min From Multiple Rows

MK_QSL — Mon, 13 May 2013 15:55:33 GMT

I have below two tables…

Part Details:

PART	Annual Sale	PRICE	Supplier
Part 01	115	$ 100.00	A
Part 01	115	$ 120.00	B
Part 01	115	$ 115.00	C
Part 02	40	$ 75.00	A
Part 02	40	$ 70.00	B
Part 02	40	$ 73.00	C
Part 03	450	$ 42.00	A
Part 03	450	$ 43.00	B
Part 03	450	$ 41.00	C
Part 04	360	$ 38.00	A
Part 04	360	$ 32.00	B
Part 04	360	$ 36.00	C
Part 05	1200	$ 15.00	A
Part 05	1200	$ 20.00	B
Part 05	1200	$ 25.00	C
Part 06	185	$ 30.00	A
Part 06	185	$ 32.00	B
Part 06	185	$ 40.00	C

Supplier Details:

Supplier	Discount	Freight
A	10%	2%
B	8%	1%
C	11%	3%

Need Final Output like this…

Where,

Net Price = Price * (1 + Freight%) * (1 – Discount%)

OUTPUT 01:

PART	Cheapest Supplier	Cheapest Supplier's Net Price	Annual Sale	TOTAL
Part 01	A	$ 91.80	115	$ 10,557.00
Part 02	B	$ 65.04	40	$ 2,601.76
Part 03	C	$ 37.58	450	$ 16,913.12
Part 04	B	$ 29.73	360	$ 10,702.80
Part 05	A	$ 13.77	1200	$ 16,524.00
Part 06	A	$ 27.54	185	$ 5,094.90

OUTPUT 02:

Supplier	Number of Part where Cheapest Supplier	Annul Sale Total
A	3	$ 32,175.90
B	2	$ 13,304.56
C	1	$ 16,913.12

Appriciate you urgent help..

Re: Find Min From Multiple Rows

Mon, 13 May 2013 16:35:10 GMT

Hi,

For OUTPUT 01, use Part as Dimension, then i think you should use an expression like ONLY({<Supplier={"=rank(-min(Price),Supplier)=1"}>}Supplier) for Cheapest Supplier, which means that you should only take the Supplier that has the lowest Price. then you probably can use an expression like ONLY({<Supplier={"=rank(-min(Price),Supplier)=1"}>}Price*(1+Freight%)*(10Discount%)) for the Cheapest Supplier's Net Price and ONLY({<Supplier={"=rank(-min(Price),Supplier)=1"}>}Annual Sale) as Annual Sale. The Total column can be determined by column(2)*column(3).

Hope this can help you creating this output.

Re: Find Min From Multiple Rows

MK_QSL — Mon, 13 May 2013 16:41:30 GMT

I have made variables and assigned respective values.

Discount : vDisA, vDisB and vDisC

Freight : vFreightA, vFreightB, vFreightC

For the first OUTPUT table…

I have used below expressions

Cheapest Supplier = FirstSortedValue(Supplier,

if(Supplier='A',PRICE*(1+vFreightA)*(1-vDisA),
if(Supplier='B',PRICE*(1+vFreightB)*(1-vDisB),
PRICE*(1+vFreightC)*(1-vDisC)))

Net Price = =FirstSortedValue(if(Supplier='A',PRICE*(1+vFreightA)*(1-vDisA),
if(Supplier='B',PRICE*(1+vFreightB)*(1-vDisB),
PRICE*(1+vFreightC)*(1-vDisC)
)),

(if(Supplier='A',PRICE*(1+vFreightA)*(1-vDisA),

if(Supplier='B',PRICE*(1+vFreightB)*(1-vDisB),
PRICE*(1+vFreightC)*(1-vDisC))))

But don’t have idea how to get 2^nd OUTPUT table.

Re: Find Min From Multiple Rows

Gysbert_Wassenaar — Mon, 13 May 2013 17:06:36 GMT

See attached qvw

Re: Find Min From Multiple Rows

MK_QSL — Mon, 13 May 2013 17:50:14 GMT

Hi Gysbert,

Looks absolutely working.

I am little bit curious about expressions you have used in second Table...

SUM(AGGR(IF(RANK ....

Could you please be kind to give me brief idea how they are working (atleast for my example)?

Thanks.....

Re: Find Min From Multiple Rows

MK_QSL — Tue, 14 May 2013 06:28:02 GMT

can someone please explain what below expression is for?

this is from Mr Gysbert's qvw file.

sum(aggr(if(rank(-PRICE*(1+Freight/100)*(1-Discount/100))=1,1,0),PART,Supplier))

Specially, what -Price..... in RANK suggest...

Your immediate help would be highly appriciated...

Thanks

Re: Find Min From Multiple Rows

Gysbert_Wassenaar — Tue, 14 May 2013 06:45:08 GMT

PRICE*(1+Freight/100)*(1-Discount/100) is your net price. You want the minimum net price. The rank function returns the ranking, where the highest value gets rank 1. By multiplying the net price with -1 the lowest net price gets rank 1. So

rank(-PRICE*(1+Freight/100)*(1-Discount/100)) will return 1 if the net price is the lowest net price. Because the lowest net price per PART-Supplier combination is required the aggr function is needed to aggregate over PART and Supplier.

Re: Find Min From Multiple Rows

MK_QSL — Tue, 14 May 2013 07:02:11 GMT

Hi Gysbert,

Thanks again for your help. Now very well understood.