Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Example:
Row 1
Field - Package >> Value: PACK1
Field - Retail Rate >> Value: $20.00
Field - Promo >> Value: $15.00
Field - Promo Type >> Value: Type 1 (becomes this amount)
Row 2
Field - Package >> Value: PACK1
Field - Retail Rate >> Value: $20.00
Field - Promo >> Value: -$10.00
Field - Promo Type >> Value: Type 2 (Removes that amount from monthly amount)
Row 3
Field - Package >> Value: PACK1
Field - Retail Rate >> Value: $20.00
Field - Promo >> Value: -10%
Field - Promo Type >> Value: Type 3 (Removes 10% off the amount)
I need to first check if a package has multiple promos, and if so apply them in order to get a final value.
PACK 1 Retail - Promo 1 - Promo 2 - Promo 3 = $4.50
----------------------------------------
I have tried grouping by package, then count(promos). This works however moving past that to calculate how to order the promo codes for applying is were I am stuck.
Thank You
Todd
I gather you want to calculate the final value in the script? See attached, and here's the script. I'm making some assumptions about the data, like that you always start with a Type 1 (otherwise, we have no value to modify, or as I coded it, we modify the value from the previous package), and probably some other things.
[Raw]:
LOAD * INLINE [
Row, Package, Retail Rate, Promo, Promo Type
1, PACK1, 20.00, 15.00, Type 1
2, PACK1, 20.00, -10.00, Type 2
3, PACK1, 20.00, -10, Type 3
1, PACK2, 40.00, 40.00, Type 1
2, PACK2, 40.00, -50, Type 3
3, PACK2, 40.00, 10, Type 2
4, PACK2, 40.00, -20, Type 3
];
[New]:
NOCONCATENATE LOAD
"Row"
,"Package"
,"Retail Rate"
,if("Promo Type"='Type 1',"Promo"
,if("Promo Type"='Type 2',peek("Value")+Promo
,if("Promo Type"='Type 3',peek("Value")*(1+"Promo"/100)))) as "Value"
RESIDENT [Raw]
ORDER BY "Package", "Row"
;
INNER JOIN ([New])
LOAD
"Package"
,max("Row") as "Row"
RESIDENT [New]
GROUP BY "Package"
;
DROP TABLE [Raw];
I gather you want to calculate the final value in the script? See attached, and here's the script. I'm making some assumptions about the data, like that you always start with a Type 1 (otherwise, we have no value to modify, or as I coded it, we modify the value from the previous package), and probably some other things.
[Raw]:
LOAD * INLINE [
Row, Package, Retail Rate, Promo, Promo Type
1, PACK1, 20.00, 15.00, Type 1
2, PACK1, 20.00, -10.00, Type 2
3, PACK1, 20.00, -10, Type 3
1, PACK2, 40.00, 40.00, Type 1
2, PACK2, 40.00, -50, Type 3
3, PACK2, 40.00, 10, Type 2
4, PACK2, 40.00, -20, Type 3
];
[New]:
NOCONCATENATE LOAD
"Row"
,"Package"
,"Retail Rate"
,if("Promo Type"='Type 1',"Promo"
,if("Promo Type"='Type 2',peek("Value")+Promo
,if("Promo Type"='Type 3',peek("Value")*(1+"Promo"/100)))) as "Value"
RESIDENT [Raw]
ORDER BY "Package", "Row"
;
INNER JOIN ([New])
LOAD
"Package"
,max("Row") as "Row"
RESIDENT [New]
GROUP BY "Package"
;
DROP TABLE [Raw];