Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Finding Column Name having minimum value

Need help in finding cheapest source for below table.

Product

Supplier01

Supplier02

Supplier03

Apple

10

20

12

Banana

13

15

8

Orange

7

14

7

Grapes

14

10

12

Only three supplier is given but actual data is having more than 100 suppliers for 10000 products.

The output should be as below as last two columns are showing results.

Product

Supplier01

Supplier02

Supplier03

Minimum Price

Cheapest Supplier

Apple

10

20

12

10

Supplier01

Banana

13

15

8

8

Supplier03

Orange

7

14

7

7

Supplier01, Supplier03

Grapes

14

10

12

10

Supplier02

Regards,

Manish Kachhia

9 Replies
Not applicable

Re: Finding Column Name having minimum value

See if it's this.

Not applicable

Re: Finding Column Name having minimum value

Thanks for your answer. Looks helpful in case 3 or 4 suppliers. But as I mentioned in my question, we have arond 100 suppliers and it's hectic to use if formula.

Is there any other solution?

Not applicable

Re: Finding Column Name having minimum value

is there anyone who can help me.

Appriciate a lot...

whiteline
Honored Contributor II

Re: Finding Column Name having minimum value

Hi.

The easiest ways to solve it osis to transform your data this way:

Product,  Supplier, Price

Apple, Supplier01, 10

Apple, Supplier02, 20

...

Then you could use  Product,  Supplier as dimensions,

and simply =min(Price) for  minimum price and =FirstSortedValue(Supplier, Price) for supplier name.

(read the manual for firstsortedvalue(), as it works if prices are different)

add:

Just use CrossTable() statement before your load statement (look at help).

Not applicable

Re: Finding Column Name having minimum value

Thanks for your response.

Please check attachment.

I have found solution using if statement, but it’s very problematic  as I have more than 100 supplier and can’t write 100 times IF sentence.

If there any other way to get solution.

Also, in this example, Cheapest Supplier for Orange should be Supplier01, Supplier03 both.

MVP & Luminary
MVP & Luminary

Re: Finding Column Name having minimum value

Is your spreadsheet with 100 suppliers in the same format, i.e only one row per product and all suppliers in columns?

You should try to get the data in the form Product, Supplier, Price. One row for every product-supplier-price combination.

Just as Whiteline said above. It's best if you can do that before you load the data in qlikview.


talk is cheap, supply exceeds demand
Not applicable

Re: Finding Column Name having minimum value

I have tried crosstable and firstsortedvalue but it gives supplier name null when the minimum prices shared by two different suppliers.

Let me know what can be done to get both supplier's name in this case.

whiteline
Honored Contributor II

Re: Finding Column Name having minimum value

Try something like this (Product as dimension):

=Concat({$<Price=min(Price)>} Supplier)

Message was edited by: whiteline

Not applicable

Re: Finding Column Name having minimum value

Thanks for reply but above function giving error...