# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

MVP

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

MVP

## 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
MVP

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

MVP

## 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
MVP

## Re: Find Min From Multiple Rows

Hi Gysbert,

Thanks again for your help. Now very well understood.