Left date to be decided based on the last Month his or her paid Month

[nevilledhamsiri]

Hi Members,

I need to decide the left Month/Year of an employee based on the last Month his/her payment was done. As for my data, I have only 6 Months paid details. Some employees have received their payment up to last Months which is the Month of June as for my data (I have loaded only up to  June). These employees are active who are still in the employment but if some one has got his her payment up to Feb, then his date of resignation should be marked as 02/01/2019. If his last paid date is April 2019, his left date should be  04/01/2019 etc. If this can be done in the front end (in the script) with a created flag fine. 

Thanks

Neville

[lironbaram · Accepted solution]

hi 

have a look at the attach file 

i added this segment to the script 

left join (Sheet3)
load 1 as monthLeftFlag,
date(max(Month)) as MonthLeftTemp ,
EMP_NO
Resident Sheet3
Group By EMP_NO;
Left Join(Sheet3)
LOad date(max(Month)) as currentMonth
Resident Sheet3;

Data:
load *,
if(MonthLeftTemp=currentMonth,'Active','Left') as EMP_STATUS,
if(MonthLeftTemp<>currentMonth,MonthLeftTemp) as MonthLeft
Resident Sheet3;

drop Table Sheet3;

[nevilledhamsiri]

I cannot open & see your data structure as I have  no qlik view licenses.Could you please copy your script in the body of the mail  or in a note pad & send. Since I have no much idea in the script design, please let me know the step by step arrangement that is to be performed.

Thanks a lot

Neville

[lironbaram]

 hi 

just add the script in my first comment to the end of the script of your demo app 

and everything will work 

the logic of the script is - 

1. adding the last month of activity for each employee 

2.adding the last global month in the data set 

3.finding for each worker if his last month is like the global last month for setting his status 

[nevilledhamsiri]

Dear Lironbaram

Thanks a lot. Went by your comments & achieved my target.

Very good solution to issues such as this.

Neville