topic Re: Dynamically display value based on minimum value in other field in QlikView

Dynamically display value based on minimum value in other field

linusblomberg — Wed, 12 Feb 2014 12:38:30 GMT

Hi, I'm kind of new to using this forum but I'm drawing blanks here and really hope that someone can get me on the right path.

I have a table with competitor prices on products. Some products are sold by more than one competitior. I wish to create a new table showing only the row (competitor) with the cheapest price. I figured out how to show the lowest price but I can't figure out how to show the competitor based on the CompetitorPrice column

First Table

ItemNo	Rank	Competitor	Index	Competitor Price	Our Price	Diff	Our Stock	Competitor
188679	19	Alpha	0,98	1 690	1 729	-39	In Stock	In Stock
188679	19	Beta	1,16	1 999	1 729	270	In Stock	In Stock
188679	19	Gamma	1,61	2 790	1 729	1061	In Stock	In Stock
188679	19	Delta	1,61	2 790	1 729	1061	In Stock	In Stock
188681	17	Alpha	1,00	2 580	2 585	-5	In Stock	In Stock
188681	17	Beta	1,16	2 999	2 585	414	In Stock	In Stock
188681	17	Gamma	1,02	2 640	2 585	55	In Stock	In Stock
188643	3	Alpha	0,75	370	495	-125	In Stock	In Stock
188643	3	Delta	1,11	549	495	54	In Stock	In Stock
188649	4	Alpha	1,00	689	689	0	In Stock	In Stock
188665	16	Alpha	0,97	1 155	1 195	-40	In Stock	In Stock
188673	20	Alpha	0,98	1 550	1 579	-29	In Stock	In Stock
188673	20	Beta	1,08	1 699	1 579	120	In Stock	In Stock
188673	20	Gamma	1,01	1 590	1 579	11	In Stock	In Stock
188673	20	Delta	1,39	2 190	1 579	611	In Stock	In Stock

Desired Table

ItemNo	Rank	Competitor	Index	Competitor Price	Our Price	Diff	Our Stock	Competitor
188679	19		0,98	1690	1729	-39	In Stock
188681	17		1,00	2580	2585	-5	In Stock
188643	3		0,75	370	495	-125	In Stock
188649	4		1,00	689	689	0	In Stock
188665	16		0,97	1155	1195	-40	In Stock
188673	20		0,98	1550	1579	-29	In Stock

Now,

Price and index columns is calculated on

aggr(Min({$<Competitor-={'OurStore'}>}$(vPrice)),Timestamp, ItemNo))

Where vPrice is a variable containing the price either with or without freight.

So how can I display the corresponding Competitor to that price?

If there are more than one competitor with the cheapest price it is ok to show a blank or any competitor with that price.

I need it to be dynamic and based on the competitors in my selection which is why I haven't just calculated it in the load script.

I'm very grateful for any ideas or suggestions.

Re: Dynamically display value based on minimum value in other field

jerem1234 — Wed, 12 Feb 2014 13:40:12 GMT

I feel like if Competitor is a dimension in your chart, it should show the corresponding competitor for your chart for that price.

Could you give more info as to the structure of your table? which are dimensions and which are expressions?

May be maxString(Competitor) if its an expression?

Re: Dynamically display value based on minimum value in other field

Wed, 12 Feb 2014 13:46:23 GMT

Try the firstsortedvalue() expresssion. This returns the value to a corresponding minimum / maximum value in another field.

So in this case you would have the product as the dimension, then

=firstsortedvalue(Competitor,Price) to return the competitor associated with the lowest price.

Erica

Re: Dynamically display value based on minimum value in other field

linusblomberg — Wed, 12 Feb 2014 13:49:33 GMT

Hi yes, Competitor is a Dimension in the first chart (is there a way for me to edit my post?)

I've tried max string but that just gives me a competitor that doesn't correspond to that row.

I wanted to try to use

({$<Price*={$(=(aggr(Min({$<Competitor-={OurStore}>}$(vPrice)),Timestamp, ItemNo)))}>}Competitor)

But that only worked when only one item was selected.

Re: Dynamically display value based on minimum value in other field

linusblomberg — Wed, 12 Feb 2014 13:55:28 GMT

Thanks Erica, I actually tried the firstsortedvalue() but you are right it does seem to work.

FirstSortedValue(Competitor,$(vPrice))

Re: Dynamically display value based on minimum value in other field

linusblomberg — Wed, 12 Feb 2014 14:16:28 GMT

And to remove 'OurStore'

FirstSortedValue({$<Competitor-={'OurStore'}>}Competitor,$(vPrice))

Thanks for quick replies