Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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

View solution in original post

1 Reply
johnw
Champion III
Champion III

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

View solution in original post