Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I am facing challenge to get active employee count between the dates. Please find the requirements below.
EMP_DIM_KEY | EMP_STRT_DT | EMP_STATUS_DT | EMP_STATUS |
1001 | 10/1/2018 | 10/1/2018 | Pending |
1001 | 10/1/2018 | 10/15/2018 | Active |
1001 | 10/1/2018 | 12/11/2018 | Active |
1001 | 10/1/2018 | 1/10/2019 | Discontinue |
1002 | 10/12/2018 | 10/12/2018 | Pending |
1002 | 10/12/2018 | 11/12/2018 | Active |
1002 | 10/12/2018 | Active | |
1003 | 10/14/2018 | 10/14/2018 | Pending |
1003 | 10/14/2018 | 11/7/2018 | Active |
1003 | 10/14/2018 | 2/2/2019 | Discontinue |
Here, Based on EMP_STATUS_DT Employee 1001 is active in October 2018 and still active up to Dec 2018. Means we have to show this guy in Oct, Nov, Dec.
Same Way others to and do sum for total.
Expected Result.
Oct 2018 - 1 (1001)
Nov 2018 -3 (1001,1002,1003)
Dec 2018 -3 (1001,1002,1003)
Jan 2019 -2 (1002,1003)
Feb 2019 -1 (1003)
Mar 2019 -1 (1003)
Apr 2019 -1 (1003)
Would you be open to transforming your data in the script? By transforming the data, you would be making this problem much more simpler for yourself.
Try this script
Table: LOAD EMP_DIM_KEY, EMP_STRT_DT, Date(If(Len(Trim(EMP_STATUS_DT)) = 0, Today(), EMP_STATUS_DT)) as EMP_STATUS_DT, EMP_STATUS; LOAD * INLINE [ EMP_DIM_KEY, EMP_STRT_DT, EMP_STATUS_DT, EMP_STATUS 1001, 10/1/2018, 10/1/2018, Pending 1001, 10/1/2018, 10/15/2018, Active 1001, 10/1/2018, 12/11/2018, Active 1001, 10/1/2018, 1/10/2019, Discontinue 1002, 10/12/2018, 10/12/2018, Pending 1002, 10/12/2018, 11/12/2018, Active 1002, 10/12/2018, , Active 1003, 10/14/2018, 10/14/2018, Pending 1003, 10/14/2018, 11/7/2018, Active 1003, 10/14/2018, 2/2/2019, Discontinue ]; FinalTable: LOAD *, Date(If(EMP_DIM_KEY = Previous(EMP_DIM_KEY), Peek('EMP_STATUS_DT'), Today())) as EMP_STATUS_DT_END Resident Table Order By EMP_DIM_KEY, EMP_STATUS_DT desc; DROP Table Table; FinalFinalTable: LOAD *, Date(EMP_STATUS_DT + IterNo() - 1) as DATE, MonthName(EMP_STATUS_DT + IterNo() - 1) as MONTHYEAR, If(Date(EMP_STATUS_DT + IterNo() - 1) = Floor(MonthEnd(EMP_STATUS_DT + IterNo() - 1)) or Date(EMP_STATUS_DT + IterNo() - 1) = Today(), 1, 0) as MONTH_END_FLAG Resident FinalTable While EMP_STATUS_DT + IterNo() - 1 <= EMP_STATUS_DT_END; DROP Table FinalTable;
and the expression
=Count(DISTINCT {<EMP_STATUS = {'Active'}, MONTH_END_FLAG = {1}>} EMP_DIM_KEY)