topic Re: MonthStart and Iterno with conditions in QlikView

MonthStart and Iterno with conditions

jonathanrichey — Wed, 17 Oct 2018 13:02:19 GMT

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');

Re: MonthStart and Iterno with conditions

sunny_talwar — Wed, 17 Oct 2018 17:40:22 GMT

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'));

Re: MonthStart and Iterno with conditions

jonathanrichey — Wed, 17 Oct 2018 17:52:07 GMT

Thank you Sunny!