As per the script given below, I need to fix the date join once following conditions are satisfied
**For currentMonth , paid needs to be zero of the employee.
Can you please combined both in a if statement?.
LOAD BR_Code, Month,MONTH(Month)as tr_month,YEAR(Month) as TR_YEAR,EPF_NO,Repeat('0', 6-len(EPF_NO))& EPF_NO as EPF,
EMP_NO,Repeat('0',6-LEN(EMP_NO))&EMP_NO AS EMP ,[Employee_ Name], PAID FROM [D:\PAY_SUMMERIES.xlsx] (ooxml, embedded labels, table is Sheet3);
left join (Sheet3) load 1 as monthLeftFlag, date(max(Month)) as MonthLeftTemp , date(Min(Month)) as MonthjoinTemp, 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, if(MonthjoinTemp<>currentMonth,MonthjoinTemp)as MonthJoin Resident Sheet3;
Neville, did Desh's last post get things working? If so, please use the Accept as Solution button on that post to mark that as the soluiton and give Desh credit for the help provided. If you figured out something else, consider posting that and mark it, and if you are still working on things, leave an update on where things are.
To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question. I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.