We have two record set as an input:
1. key_1, key_2, prio, amount_1
2. key_2, amount_2
And look for this output
1. key_1, key_2, amount_3
under following rules:
- sort records by: prio, key_1, amount_2
- (prio is attribut of key_1 only, not relation of key_1, key_2)
- calculate amount_3 as min(amount_1, amount_2)
- decrease both amount_1 and amount_2 by amount_3 for the next record/row
- do until amount_3 > 0
Dummy feeling is , that it shall be possible to do it with two crossed OLAP functions, but we were not successful, then playing with recursive functionlity, but also without right results.
Thanks a lot for tips&hints
PS: Business background - it's about multiple collaterals allocation to multiple credit lines.
Can you please provide an example of the input data and the required output, so that we can understand it better