OLAP functions in M:N relation

Database

OLAP functions in M:N relation

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

Petr

PS: Business background - it's about multiple collaterals allocation to multiple credit lines.

1 REPLY

Re: OLAP functions in M:N relation

Hi Petr,

Can you please provide an example of the input data and the required output, so that we can understand it better

Regards

Rajeev