Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have two tables
Table 1:
It consists of EmployeeID and Date of joining of the employee:
LOAD [EmpID] as EmployeeID,
date(date(left(EmpDataofJoining, len(EmpDataofJoining)-9)),'MM/DD/YYYY') as DateofJoining
FROM
Ava_EmployeeMasterList.csv
(txt, unicode, embedded labels, delimiter is '\t', msq);
Table 2:
It consists of EmployeeID and RelievingDate of the employee:
LOAD EmployeeID,
If(Len(Trim(RelievingDate)) = 0, Date(Today(),'MM/DD/YYYY'), Date(RelievingDate,'MM/DD/YYYY')) as RelievingDate
FROM
EPE_LSTRelievingFormInfo.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
The requirement is:
I want to column as Date which consists of dateofjoining as a minimum date and
if the employee is relieved maximum as RelievingDate or
if the employee is not relieved maximum as today
For example I have filter employeeID 138
the employee was joined in the organization on 08/16/2016 and not relieved and working on the organization. so I need a column Date consists of all the dates from 08/16/2016 to 05/29/2017.
I have used the below script
EmployeeInfo:
LOAD [EmpID] as EmployeeID,
date(date(left(EmpDataofJoining, len(EmpDataofJoining)-9)),'MM/DD/YYYY') as DateofJoining
FROM
Ava_EmployeeMasterList1.csv
(txt, unicode, embedded labels, delimiter is '\t', msq);
join
LOAD EmployeeID,
If(Len(Trim(RelievingDate)) = 0, Date(Today(),'MM/DD/YYYY'), Date(RelievingDate,'MM/DD/YYYY')) as RelievingDate
FROM
EPE_Data\EPE_LSTRelievingFormInfo1.csv
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
presentEmp:
LOAD EmployeeID,
DateofJoining as DateofJoining1,
RelievingDate as RelievingDate1
Resident EmployeeInfo;
MinMax:
LOAD Min(DateofJoining1) as Min,
Max(RelievingDate1) as Max
Resident presentEmp;
LET vMin = Peek('Min');
LET vMax = Peek('Max');
DROP Table MinMax;
Calendar:
LOAD Date(Date,'MM/DD/YYYY') as Date ,
Date as MonthYear;
LOAD Date($(vMin) + IterNo() - 1) as Date
AutoGenerate 1
While $(vMin) + IterNo() - 1 <= $(vMax);
Left Join (presentEmp)
IntervalMatch (Date)
LOAD DateofJoining1,
RelievingDate1
Resident presentEmp;
DROP Fields DateofJoining1,RelievingDate1;
But it is not working it is showing as not associated
Anyone can help me!
I have attached my sample data along with this
Regards
Yoganantha Prakash G P
