Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've trying to do this in the script, managed to do it with an expression.
I want to find the lowest priced product per SubId and apply a discount (in this case 50%) and then sum the Totals.
FirstsortedValue should work i guess but I'm doing something wrong 🙂
This is the expression:
(Sum(FullPrice)
-
(Min({<flgNewOrder = {'1'}>} Aggr(FullPrice, [Product ID]))
*0.5))
SubId | ProdId | FullPrice | PriceAfterDiscount |
4506 | 23 | 268 | 268 |
4506 | 54 | 226 | 226 |
4506 | 12 | 145 | 72,5 |
Total | 639 | 566,5 |
Problem is confusing, yes. But you know what is making this more difficult is the changing requirement. It seem you/your user are building onto the problem. It would be easier if we know the whole issue and try to resolve it instead of breaking it down.
Anyways, try this
Table:
LOAD SubId,
ProdId,
Fullprice as FullPrice,
DiscountPrice,
FinalPrice
FROM
[C:\Users\talwars\Downloads\DiscountProd.xlsx]
(ooxml, embedded labels, table is Blad1);
FinalTable:
LOAD *,
If(SubId = Previous(SubId), RangeSum(Peek('CountProdId'), 1), 1) as CountProdId,
If(If(SubId = Previous(SubId), RangeSum(Peek('CountProdId'), 1), 1) <= 2, DiscountPrice, FullPrice/2) as PriceAfterDiscount
Resident Table
Order By SubId, DiscountPrice desc;
DROP Table Table;
Perhaps this?
If(FullPrice=Min(TOTAL <SubId> FullPrice),
((Sum(FullPrice)-(Min({<flgNewOrder = {'1'}>} Aggr(FullPrice, [Product ID]))*0.5))*(50/100)),
(Sum(FullPrice)-(Min({<flgNewOrder = {'1'}>} Aggr(FullPrice, [Product ID]))*0.5)))
Thanks for the reply,
it kind of works but I'm trying to move the logic to the script.
Trying to nest firstsortedvalue in the script but I'm getting an error. It's a bit of a different question but I'm trying another approach.
Load
*,
if(flgFrameDiscount=1,FirstSortedValue(FullPrice,FullPrice)*0.5,
FirstSortedValue(DiscPrice,DiscPrice)*0.5) as DiscountedPrice
Group by SubId;
Try this
T1:
Load SubId, ProdId, FullPrice From T1;
Left Join (T1)
Load SubId, If(Min(FullPrice)=FullPrice, FullPrice*0.5, FullPrice) as PriceAfterDiscount Resident T1 Group By SubId;
Andreas, did Anil's latest post get you what you needed? If so, do not forget to return to the thread and use the Accept as Solution button on that post to give Anil credit for the help and to let others know that did work. If you are still working upon things, leave an update.
Regards,
Brett
This is one option you can try
Table:
LOAD * INLINE [
SubId, ProdId, FullPrice
4506, 23, 268
4506, 54, 226
4506, 12, 145
];
FinalTable:
LOAD *,
If(SubId = Previous(SubId), FullPrice, FullPrice/2) as PriceAfterDiscount
Resident Table
Order By SubId, FullPrice;
DROP Table Table;
Thanks for the replies.
Things have gotten a bit more complicated so that's why I haven't responded.
I now have several scenarios when I need to apply discount to products.
1. If the SubId has 1-2 products no discount should be applied
2. If the SubId has 3 products, find the one with the lowest price and apply 50% discount to that product.
3. If the SubId has 4 or more (could be up to 10) products. Apply 50% discount to all products except the two most expensive ones. So if one SubId has 6 Products then 50% discount should be applied to the 4 cheapest products.
And I also need to be able to sum both the total for the SubIds and ProdIds with and without discount so that I can show the difference in %.
May be this
Table:
LOAD * INLINE [
SubId, ProdId, FullPrice
4506, 23, 268
4506, 54, 226
4506, 12, 145
4507, 23, 268
4507, 54, 226
4508, 23, 268
4508, 54, 226
4508, 12, 145
4508, 92, 223
];
FinalTable:
LOAD *,
If(SubId = Previous(SubId), RangeSum(Peek('CountProdId'), 1), 1) as CountProdId,
If(If(SubId = Previous(SubId), RangeSum(Peek('CountProdId'), 1), 1) <= 2, FullPrice, FullPrice/2) as PriceAfterDiscount
Resident Table
Order By SubId, FullPrice desc;
DROP Table Table;
Thanks Sunny,
I'm doing some experimenting with your code now, I'll let you know how it turns out 🙂
Thanks for your time,
I'm attaching a sample file since it again has gotten a bit more complicated.
the first three SubId's in the file should look for if there's is a Discount in the DiscountPrice row. Then apply 50% to the Fullprice row of the lowest value of the Fullprice andDiscountPrice rows.
For 4506 Fullprice is 255,230,213 but the DiscountPrice is 209. Therefore 50% discount is applied to the Fullprice 230 and Finalprice is 115 for ProdId 124.
SubId 4509 has no DiscountPrice so it should just apply 50% Discount to the lowest ProdId in the Fullprice row which is 173 and comes out to a FinalPrice of 86,5.
Confusing? Yes a bit and I can't seem to the get the sum per ProdId and SubId correct 🙂