topic Re: Find Min From Multiple Rows Part 2 in QlikView

Find Min From Multiple Rows Part 2

MK_QSL — Wed, 15 May 2013 12:11:27 GMT

Two days before, I have asked a question.. I need little bit modification in this.

You can find it from below link.

http://community.qlik.com/message/345399#345399

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 01	115	$ 95.00	D
Part 01	115	$ 115.00	E
Part 02	40	$ 75.00	A
Part 02	40	$ 70.00	B
Part 02	40	$ 60.00	C
Part 02	40	$ 60.00	D
Part 02	40	$ 80.00	E
Part 03	450	$ 42.00	A
Part 03	450	$ 43.00	B
Part 03	450	$ 41.00	C
Part 03	450	$ 40.50	D
Part 03	450	$ 47.00	E
Part 04	360	$ 38.00	A
Part 04	360	$ 32.00	B
Part 04	360	$ 36.00	C
Part 04	360	$ 40.00	D
Part 04	360	$ 42.00	E
Part 05	1200	$ 15.00	A
Part 05	1200	$ 12.50	B
Part 05	1200	$ 25.00	C
Part 05	1200	$ 27.00	D
Part 05	1200	$ 14.00	E
Part 06	185	$ 30.00	A
Part 06	185	$ 29.00	B
Part 06	185	$ 40.00	C
Part 06	185	$ 35.00	D
Part 06	185	$ 42.00	E

Supplier Details:

Supplier	Discount	Freight	Supplier Type
A	10.00%	2.00%	Main
B	8.00%	1.00%	Alternate
C	11.00%	3.00%	Main
D	9.00%	4.00%	Alternate
E	12.00%	2.50%	Main

Need Final Output like this…

Where,

1) Net Price = Price * (1 + Freight%) * (1 – Discount%)
2) Alternate Supplier (i.e. B & D) can be cheapest only if Net Price of Alternate Supplier is lower than or equal to 7% compare to Main Supplier (i.e. A, C & E) for respective part. i.e. ((Net Price of Alternate)-(Net Price of Main))/ (Net Price of Main) <=7%

OUTPUT 01:

PART	Cheapest Supplier	Net Price	Annual Sale	TOTAL
Part 01	A	$ 91.80	115	$ 10,557.00
Part 02	C	$ 55.00	40	$ 2,200.00
Part 03	C	$ 37.58	450	$ 16,911.00
Part 04	B	$ 29.73	360	$ 10,702.80
Part 05	B	$ 11.62	1200	$ 13,944.00
Part 06	A	$ 27.54	185	$ 5,094.90
$ 59,409.70

OUTPUT 02:

Supplier	Number of Parts where Cheapest	Total
A	1	$ 15,651.90
B	1	$ 24,646.80
C	2	$ 19,111.00
$ 59,409.70

Appreciate you urgent help..

Re: Find Min From Multiple Rows Part 2

MK_QSL — Wed, 15 May 2013 13:06:34 GMT

I have enclosed qvw file.

Re: Find Min From Multiple Rows Part 2

MK_QSL — Wed, 15 May 2013 14:21:57 GMT

I have managed to get OUTPUT table 1... Please check PART 2 with OUTPUT table 1 file.

Little correction in second where cluase..

2) Alternate Supplier (i.e. B & D) can be cheapest only if Net Price of Alternate Supplier is lower than or equal to 7% compare to Main Supplier (i.e. A, C & E) for respective part.

i.e. ((Net Price of Main)-(Net Price of Alternate))/ (Net Price of Main) >=7%

Re: Find Min From Multiple Rows Part 2

Wed, 15 May 2013 15:34:00 GMT

I'm not really sure if I understand your question correctly but use set analysis to compare prices between main and alternate suppliers:

{<SUPPLIER={'B','D'}>}

{<SUPPLIER-={'B','D'}>}

Re: Find Min From Multiple Rows Part 2

MK_QSL — Wed, 15 May 2013 15:47:31 GMT

OUTPUT TABLE 01

Purpose is to find cheapest source/supplier for each part, but alternate source can only be cheapest if they are cheaper by 7% or more compare to Main source.

Means, part wise cheapest sorce....

OUTPUT TABLE 02

Here I want on how may parts, each supplier is cheapest?

Hope this will clarify your confusion to help me.

Re: Find Min From Multiple Rows Part 2

MK_QSL — Thu, 16 May 2013 08:02:37 GMT

Can anyone please help?