Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
MK_QSL
MVP
MVP
‎2013-05-15 08:11 AM

Find Min From Multiple Rows Part 2

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:

SupplierDiscountFreightSupplier Type
A10.00%2.00%Main
B8.00%1.00%Alternate
C11.00%3.00%Main
D9.00%4.00%Alternate
E12.00%2.50%Main


Need Final Output like this…

Where,

  1. 1)      Net Price = Price * (1 + Freight%) * (1 – Discount%)
  2. 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..

PART 2.qvw
PART 2 with OUTPUT TABLE 1.qvw
490 Views
0 Likes
5 Replies
MK_QSL
MVP
MVP
‎2013-05-15 09:06 AM
Author

I have enclosed qvw file.

396 Views
0 Likes
MK_QSL
MVP
MVP
‎2013-05-15 10:21 AM
Author

In response to MK_QSL

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%

396 Views
0 Likes
Not applicable
‎2013-05-15 11:34 AM

In response to MK_QSL

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'}>}

396 Views
0 Likes
MK_QSL
MVP
MVP
‎2013-05-15 11:47 AM
Author

In response to

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.

396 Views
0 Likes
MK_QSL
MVP
MVP
‎2013-05-16 04:02 AM
Author

In response to MK_QSL

Can anyone please help?

396 Views
0 Likes