MIN VALUE AND COLUMN NAME

[#1]

I am having below table

ITEM	CITY 01	CITY 02	CITY 03
ITEM 01	10	20	13
ITEM 02	8	12	12
ITEM 03	24	20	14
ITEM 04	13	15	20
ITEM 05	18	14	14
ITEM 06	20	16	24
ITEM 07	24	18	13
ITEM 08	26	20	12

Need answer as below showing MINIMUM VALUE against EACH line.

ITEM	CITY 01	CITY 02	CITY 03	MIN PRICE	CITY HAVING MIN PRICE
ITEM 01	10	20	13	10	CITY 01
ITEM 02	8	12	12	8	CITY 01
ITEM 03	24	20	14	14	CITY 03
ITEM 04	13	15	20	13	CITY 01
ITEM 05	18	14	14	14	CITY 02, CITY 03
ITEM 06	20	16	24	16	CITY 02
ITEM 07	24	18	13	13	CITY 03
ITEM 08	26	20	12	12	CITY 03

Thanks in advance...

Regards,

Manish

[Jason_Michaelides]

Is CITY a dimension with CITY01, CITY02, CITY03 as values?

Can you post your app?

[#3]

Dear Jason, CITY1, CITY2, CITY3 are dimensions.

[Jason_Michaelides]

In that case you would be best to crosstable the data.  Can you post some sample data and your app so far?  Easier to help then.

[CELAMBARASAN]

Hi,

     You can try with this,

          Have a straight table with the dimensions item, city 01, city 02, city 03

          with expression

               Min Price = RangeMin([CITY 01],[CITY 02],[CITY 03])

               City having Min Price = If([CITY 01] = Column(1), 'CITY 01' & if([CITY 02] = Column(1), ',CITY 02', If([CITY 03] = Column(1), ',CITY 03','')), If([CITY 02] = Column(1), 'CITY 02' & if([CITY 03] = Column(1), ',CITY 03'),

'CITY 03'))

Hope it helps

Celambarasan

[#6]

Dear Celambarasan,

Appriciate the way you have answered but I have around 30 to 40 CITY dimensions out of which I have given only 3.

Is there any another way.

Regards,

Manish

[CELAMBARASAN]

Hi,

     Getting the field name of min value from the 40 dimensions is tedious one because as of me there is no method to get the field name based on the value. If you got any other help let me know.

Celambarasan

[Jason_Michaelides]

Hi Manish,

The attached uses Crosstable() to get however many CITIES there are into a tall thin table.  It is then possible to get the minimum price into a chart, which I have done. The only thing I haven't got working yet is the concatenated string of cities that have the minimum price for each item.  I know you need to use Concat() but I can't get it to limit the values...I'll have another go later.

Anyway - hope this helps.

Jason

[#9]

Thanks for your reply...

[Jason_Michaelides]

Hi Manish,

I've managed to complete this for you by adding another bit to the script.  The MinPrice will not be dependent upon other selections by doing it this way but maybe that's OK for you...

Hope it helps,

Jason