Apply discount to the product with the lowest value.

[qlikster]

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

[Anil_Babu_Samineni]

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)))

[qlikster]

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;

[Anil_Babu_Samineni]

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;

[Brett_Bleess]

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

[sunny_talwar]

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;

[qlikster]

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 %.

[sunny_talwar]

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;

[qlikster]

Thanks Sunny,

I'm doing some experimenting with your code now, I'll let you know how it turns out 🙂

[qlikster]

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 🙂