Need help with group by and applying fields in a certain order

[#1]

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

[johnw · Accepted solution]

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];