Find out optimize price if Possible

[rishikeshtiwari]

I have a table having 5 fields.

I am trying to find optimized Prices.Suppose An item I1 has been sold by 3 Vendors V1,V2,V3 in our table. Unit Prices are also different for all the Vendors. In this case, we need to find out the minimum price for that Particular item I1.

So our requirement is to find out the minimum price and Subtract this minimum price into all the individual prices for that Particular Item for all the Vendors. It is the case for I1 to I3.

But see the I6,I7,I8 we are analyzing that Unit price is same for all the vendors for those respective items.So in this case we need't substract the price. Because all the prices for all the Vendors are same for respective Items.So we need as it is the price.I have attached the result in Excel sheet.

I have find out the subtraction but not able to isolate or restrict the values for those Items which Unit prices are same.

[sunny_talwar]

FinalTest.qvw seems to be all empty: No Script and nothing available on the front end. Was that the intention?

[effinty2112]

Hi Rishi,

Is this what you want?

Item	Vendor	Unit_Price	Expr1
I1	V1	10	0
I1	V2	20	10
I1	V3	30	20
I2	V1	10	0
I2	V2	20	10
I2	V3	30	20
I2	V4	40	30
I3	V1	15	0
I3	V2	25	10
I3	V3	35	20
I3	V4	45	30
I3	V5	55	40
I6	V12	110	110
I6	V13	110	110
I7	V14	100	100
I7	V15	100	100
I7		100	100
I8	V16	900	900

Expr1:

if(count(Total<Item> DISTINCT Unit_Price) >1,

Unit_Price -  Min(TOTAL <Item>Unit_Price),

Unit_Price)

Regards

Andrew

[tamilarasu]

Hi,

Try this,

Source:
LOAD Item,
Vendor,
Unit_Price,
Quantity
FROM
[MIN_TEST_DATA.xlsx]
(ooxml, embedded labels, table is [Testing data]);

Left Join

Load Item,
Min(Unit_Price) as Min_Unit_Price,
Max(Unit_Price) as Max_Unit_Price
Resident Source Group by Item;

Final:
Load Item,
Vendor,
Unit_Price,
Quantity,
If(Min_Unit_Price = Max_Unit_Price, Unit_Price, Unit_Price-Min_Unit_Price) as Result
Resident Source;

DROP Table Source;

[rishikeshtiwari]

Thank you so much Tamil Nagaraj.

[rishikeshtiwari]

Thank you so much Andrew.

[tamilarasu]

Hi Rishi,

Did you get solution.? If yes, please close thread by marking correct/ helpful answers. If not, please let us know.