MonthStart and Iterno with conditions

[jonathanrichey]

I am trying to update the script below with two requirements:

1. Start interno on Jan 2015

2. End interno on the current month

Any help would be greatly appreciated.

Below is the script. I have also attached the qvw and excel spreadsheet with the expceted output.

Employee:
LOAD * INLINE [
    Employee_Number, Date_Hired, Term_Date
    119311, Nov 2012, Feb 2023
];

Left Join (Employee)
tEmployeeStatus:
Load
Employee_Number,
MonthStart(addmonths(date#([Date_Hired],'MMM YYYY'), ITERNO()-1)) as [Month Start]
Resident Employee
While addmonths(date#([Date_Hired],'MMM YYYY'), ITERNO()-1)<= date#([Term_Date],'MMM YYYY');

[sunny_talwar · Accepted solution]

Try this

Employee:

LOAD * INLINE [

    Employee_Number, Date_Hired, Term_Date

    119311, Nov 2012, Feb 2023

];

Left Join (Employee)

LOAD *

Where [Month Start] >= MakeDate(2015);

//tEmployeeStatus:

LOAD Employee_Number,

MonthStart(AddMonths(Date#([Date_Hired], 'MMM YYYY'), IterNo()-1)) as [Month Start]

Resident Employee

While AddMonths(Date#([Date_Hired], 'MMM YYYY'), IterNo()-1) <= RangeMin(MonthStarT(Today()), Date#([Term_Date], 'MMM YYYY'));

[jonathanrichey]

Thank you Sunny!