Really need your help with a coding challenge I'm facing. I'm a coding novice, but need assistance coding the following in the loadscript:
I need to calculate a sequential underwriting year and the start and end dates of an underwriting year by extrapolating from the original policy inception date.
As an example, if a client incepted coverage on 01 August 2020 (ie original policy inception date), the underwriting years would be calculated as follows:
1 > 01 Aug 2020 to 31 Jul 2021
2 > 01 Aug 2021 to 31 Jul 2022
3 > 01 Aug 2022 to 31 Jul 2023
And so forth.
Currently, I only avail of the original inception date and the assumption that all policy periods are 12 months.
Could you try this script? I have put some random dates in the inline. Hence, adjust it for the values you need.
A:
LOAD Customer, Subscription
INLINE [
Customer, Subscription
A, 23/09/2020
B, 23/02/2020
C, 30/03/2021
D, 31/03/2021
E, 01/04/2022
F, 02/04/2022
G, 03/04/2023
H, 06/06/2023
I, 07/06/2024
J, 08/06/2025
K, 09/06/2025
L, 10/06/2025
];
A1:
LOAD Min(Year(Date(Date#(Subscription,'DD/MM/YYYY'),'DD/MM/YYYY'))) as Min_Year
Resident A;
LET vYearsMin = peek('Min_Year',0,'A1');
LET vYears = Year(Today()) - $(vYearsMin);
For i=0 to $(vYears)
B:
LOAD Customer,
AddMonths(Date(Date#(Subscription,'DD/MM/YYYY'),'DD/MM/YYYY'),12*($(i))) as Subscription_Start_Period, AddMonths(Date(Date#(Subscription,'DD/MM/YYYY'),'DD/MM/YYYY'),12*($(i)+1)) as Subscription_End_Period, $(i)+1 as Underwriting
RESIDENT A
Where AddMonths(Date(Date#(Subscription,'DD/MM/YYYY'),'DD/MM/YYYY'),12*$(i))<=Date(Today(),'DD/MM/YYYY');
Could you try this script? I have put some random dates in the inline. Hence, adjust it for the values you need.
A:
LOAD Customer, Subscription
INLINE [
Customer, Subscription
A, 23/09/2020
B, 23/02/2020
C, 30/03/2021
D, 31/03/2021
E, 01/04/2022
F, 02/04/2022
G, 03/04/2023
H, 06/06/2023
I, 07/06/2024
J, 08/06/2025
K, 09/06/2025
L, 10/06/2025
];
A1:
LOAD Min(Year(Date(Date#(Subscription,'DD/MM/YYYY'),'DD/MM/YYYY'))) as Min_Year
Resident A;
LET vYearsMin = peek('Min_Year',0,'A1');
LET vYears = Year(Today()) - $(vYearsMin);
For i=0 to $(vYears)
B:
LOAD Customer,
AddMonths(Date(Date#(Subscription,'DD/MM/YYYY'),'DD/MM/YYYY'),12*($(i))) as Subscription_Start_Period, AddMonths(Date(Date#(Subscription,'DD/MM/YYYY'),'DD/MM/YYYY'),12*($(i)+1)) as Subscription_End_Period, $(i)+1 as Underwriting
RESIDENT A
Where AddMonths(Date(Date#(Subscription,'DD/MM/YYYY'),'DD/MM/YYYY'),12*$(i))<=Date(Today(),'DD/MM/YYYY');
