Hello All,
I'm having quite tough time in achieving this, plz Plz anyone help me on this.
I have attached the sample data.
I need to show Count of Active, Inactive and Joined employees with all the scripting in Backend because of performance issue and in frontend I could acheive the requirement.
Active Employees : If i select on the Feb-2005, it should show the Count of No of employees active from thr min date Feb-2002 till the selection made
Inactive Employees : for example, Emp_Code - H12 should show the count in inactive only if the selections made on or after this date - 04-09-2018
Till then the employee should be in active count.
Joined : Count of employees Joined per month or based on the selection.
Below is the script i have used till now for backend implementation.
Employee:
First 10
LOAD
Emp_Code,
COMPANY_CODE,
COMPANY_NAME,
Department,
Date(num(floor(DATE_OF_JOIN))) as DOJ,
Date(num(floor(Last_working_day))) as LWD,
if(Isnull(Last_working_day),1,2) as Status_check,
if(IsNull(Last_working_day),Today(),Date(Floor(Num(Last_working_day)),'DD/MM/YYYY')) as new_Last_working_day,
// if(Isnull(Last_working_day),'Active','Inactive') as Status
//if(len(Last_working_day)=0,'Active','Inactive') as Status_Check
// Level,
// Associate,
CLIENT_NAME
// "Client code"
FROM [lib://Data_Connection/PHL CoA Emp Data.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [Employee data]);
CalendarLinkage:
LOAD
// Load Table Keys
Date(Num(Floor(DOJ))) As [Key Master_Date Date],
// Load Table Data
Emp_Code,
DOJ As Date,
'JoiningDate' As DateType
Resident Employee;
Concatenate (CalendarLinkage)
LOAD
// Load Table Keys
Date(Num(Floor(LWD))) As [Key Master_Date Date],
// Load Table Data
Emp_Code,
LWD As Date,
'LeavingDate' As DateType
Resident Employee
Where Not (IsNull(LWD));
QuartersMap:
MAPPING LOAD * Inline [
Month, Q
1, Q1
2, Q1
3, Q1
4, Q2
5, Q2
6, Q2
7, Q3
8, Q3
9, Q3
10, Q4
11, Q4
12, Q4
];
MonthMap:
Mapping LOAD * Inline [
MonthNo, LongMonth
1, January
2, February
3, March
4, April
5, May
6, June
7, July
8, August
9, September
10, October
11, November
12, December
];
Temp:
Load
min([Key Master_Date Date]) as minDate, // Replace DateKey with your Date key field
Max([Key Master_Date Date]) as maxDate // Replace DateKey with your Date key field
Resident CalendarLinkage; // Replace SourceTable with the name of the table containing the DateKey field
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
TempCalendar:
LOAD
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
Set vFM = 4 ;
MasterCalendar:
LOAD
TempDate AS [Key Master_Date Date],
Date(TempDate,'DD/MM/YYYY') as date_link,
Num(TempDate) as numdate,
Date(TempDate,'MMM-YYYY') as Month_Year,
// Replace DateKey with your Date key field
week(TempDate) as Week,
Year(TempDate) as Year,
Year(TempDate) -1 as [PriorYear],
Month(TempDate) as Month,
Dual(ApplyMap('MonthMap', Month(TempDate), Null()), Month(TempDate)) as MonthLong,
Day(TempDate) as Day,
YeartoDate(TempDate)*-1 as CurYTDFlag,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag,
inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,
date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,
ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
// Fiscal Year fields
//Year(TempDate) + If(Month(TempDate)>=$(vFM), 1, 0) As [Fiscal Year],
//Mod(Month(TempDate)-$(vFM), 12)+1 As [Fiscal Month]
Resident TempCalendar
Order By TempDate ASC;
Drop Table TempCalendar;
exit script;
