Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

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

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

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

 

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

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;

 

View solution in original post

4 Replies
Highlighted
Partner
Partner

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;

 

View solution in original post

Highlighted
Specialist
Specialist

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

Highlighted
Partner
Partner

 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 

Highlighted
Specialist
Specialist

Dear Lironbaram

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

Very good solution to issues such as this.

 

Neville