Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. 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

Highlighted
Contributor III
Contributor III

Thank you Sunny!