Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Find Min From Multiple Rows

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..

1 Solution

Accepted Solutions

Re: Find Min From Multiple Rows

See attached qvw


talk is cheap, supply exceeds demand
7 Replies
Not applicable

Re: Find Min From Multiple Rows

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

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 2nd OUTPUT table.

Re: Find Min From Multiple Rows

See attached qvw


talk is cheap, supply exceeds demand

Re: Find Min From Multiple Rows

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

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

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.


talk is cheap, supply exceeds demand

Re: Find Min From Multiple Rows

Hi Gysbert,

Thanks again for your help. Now very well understood.

Community Browser