Hey Qlik gurus,
Wondering if someone could assist with a logical issue I have at the moment. Essentially what I have is two straight tables, one called EXPOSURES, the other HEDGES. The logical requirements I need to meet are as follows: (PS. Load script and/or set analysis solution suggestions are fine, I have hacked at both with no success).
1. I need to do a cumulative sum of all the exposures (ExposureAmount) in table "Exposures" up to the point where the first hedge amount (HedgeAmount) in table "HEDGES" is fully utilized (HedgeSequence = 1). If the cumulative sum of ExposureAmounts are greater then the HedgeAmount, then it needs to be split so that the cumulative sum of ExposureAmount ends at the same value of HedgeAmount.
2. Once the HedgeAmount has been filled, then I need the remainder of the ExposureAmount to be carried over and used to start filling the second HedgeAmount.
This is quite tricky to explain, so I will give an example below:
| EXPOSURES | | |
| ReportingPeriod | Date | ExposureAmount |
| 01-2019 | 01-01-2019 | 2 |
| 01-2019 | 02-01-2019 | 3 |
| 01-2019 | 04-01-2019 | 2 |
| 01-2019 | 07-01-2019 | 5 |
| 01-2019 | 08-01-2019 | 2 |
| 01-2019 | 13-01-2019 | 3 |
| 01-2019 | 16-01-2019 | 1 |
| 01-2019 | 18-01-2019 | 8 |
| 01-2019 | 22-01-2019 | 4 |
| 01-2019 | 23-01-2019 | 7 |
| HEDGES | | |
| ReportingPeriod | HedgeAmount | HedgeSequence |
| 01-2019 | 10 | 1 |
| 01-2019 | 10 | 2 |
| 01-2019 | 10 | 3 |
| 01-2019 | 9 | 4 |
LOGIC:
cumulative sum of ExposureAmount on 1 Jan = 2, so still less than the HedgeAmount(10) where HedgeSequence = 1 , ExposureAmount = 2 and Exposure_CumSum = 2 ... (2) and AllocatedSequence = 1
cumulative sum of ExposureAmount on 2 Jan = 3, so still less than the HedgeAmount(10) where HedgeSequence = 1 , ExposureAmount = 3 and Exposure_CumSum = 5 ... (2 + 3) and AllocatedSequence = 1
cumulative sum of ExposureAmount on 4 Jan = 2, so still less than the HedgeAmount(10) where HedgeSequence = 1 , ExposureAmount = 2 and Exposure_CumSum = 7 ... (2 + 3 + 2) and AllocatedSequence = 1
cumulative sum of ExposureAmount on 7 Jan = 5, this is greater than the HedgeAmount(10) where HedgeSequence = 1 , thus ExposureAmount = 1 and Exposure_CumSum = 10 ... (2 + 3 + 2 + 3) and AllocatedSequence = 1
now... the remainder of the ExposureAmount on 7 Jan (5 - 3 = 2) needs to be allocated as follows:
ExposureAmount = 2, Exposure_CumSum = 2, AllocatedSequence = 2
... so the process continues until all ExposureAmounts have been Accumulated to fill the various HedgeAmounts.
Desired outcome (Fields ExposureAmount & Exposure_CumSum & AllocatedSequence)
| EXPOSURES_NEW: | | | |
| Period | Date | Exposure | Exposure_CumSum | AllocatedSequence |
| 01-2019 | 01-01-2019 | 2 | 2 | 1 |
| 01-2019 | 02-01-2019 | 3 | 5 | 1 |
| 01-2019 | 04-01-2019 | 2 | 7 | 1 |
| 01-2019 | 07-01-2019 | 3 | 10 | 1 |
| 01-2019 | 07-01-2019 | 2 | 2 | 2 |
| 01-2019 | 08-01-2019 | 2 | 4 | 2 |
| 01-2019 | 13-01-2019 | 3 | 7 | 2 |
| 01-2019 | 16-01-2019 | 1 | 8 | 2 |
| 01-2019 | 18-01-2019 | 2 | 10 | 2 |
| 01-2019 | 18-01-2019 | 6 | 6 | 3 |
| 01-2019 | 22-01-2019 | 4 | 10 | 3 |
| 01-2019 | 23-01-2019 | 7 | 7 | 4 |
Thank you.
