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.
