Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

MonthStart and Iterno with conditions

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

1 Solution

Accepted Solutions
Highlighted

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

View solution in original post

2 Replies
Highlighted

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

View solution in original post

Contributor III
Contributor III

Thank you Sunny!