Hi All,
This document illustrate how to generate the future dates/months based on different interval for each records. i.e. for different keys you need to generate different due dates , consider the e.g. below for Key 1 we need the future due date to be generate every 6 months and for Key 2 we need 3 month follow up ..
Input Data:
| Key | Month Interval | Amount | Due Date | Expire Date |
| 1 | 6 | 50 | 01-May-19 | 31-Dec-20 |
| 2 | 3 | 50 | 12-Dec-18 | 31-Dec-20 |
| 3 | 12 | 100 | 31-Dec-17 | 31-Dec-20 |
| 4 | 1 | 10 | 20-May-19 | 31-Dec-20 |
*For key 1 we have Month Interval as 6 so we need to dynamically generate future due date months i.e.
OUTPUT for Key 1:
| Key | Month Interval | Amount | Due Date | Future Due Date | Expire Date |
| 1 | 6 | 50 | 01/05/2019 | 01-Nov-19 | 31-Dec-20 |
| 1 | 6 | 50 | 01/05/2019 | 01-May-20 | 31-Dec-20 |
| 1 | 6 | 50 | 01/05/2019 | 01-Nov-20 | 31-Dec-20 |
Script:
DATA:
LOAD Key,
[Month Interval],
Amount,
[Due Date],
[Expire Date]
FROM
[C:\Users\avinash5\Desktop\Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
Left Join
LOAD
*,
AddMonths([Due Date] ,([Month Interval]+ ([Month Interval]*IterNo()))) as [Future Due Date]
While
AddMonths([Due Date] ,([Month Interval]*IterNo())) <= [Expire Date]; //Defines when the Interval should end
LOAD Key,
[Month Interval],
Amount,
[Due Date],
[Expire Date]
FROM
[C:\Users\avinash5\Desktop\Sample Data.xlsx]
(ooxml, embedded labels, table is Sheet1);
Note: I have fixed Expire date , you could use different dates also.
Regards,
Avinash R
