Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hiii all ....
i have table with employee position assignment, the assignment range date look from field Valid From until Valid To. The Question is : How can i generate table into new format, and employee should have much raw data Example :
Data Original : 1. Employee : Toni Position : Data Analysis Valid From : Jan 1, 2019 Valid To : March 1, 2019
i want to manipulate data like this :
1. Employee : Toni Position : Data Analysis Valid From : Jan 1, 2019 Valid To : Jan 31, 2019
2. Employee : Toni Position : Data Analysis Valid From : Feb 1, 2019 Valid To : Feb 28, 2019
3. Employee : Toni Position : Data Analysis Valid From : March 1, 2019 Valid To : March 31, 2019
THIS LOAD DATA ORIGINAL :
POSITION_ASSIGNMENT_ORI: LOAD [Emp No], [Position ID], [Position Title], Primary, [Position Class], [Company Office], [Year in Services], [Month in Services], [Valid From], [Valid To], [Assignment Type], [Join Date], [Termination Date], [Remuneration Type], [Employment Type], [Employee Name], [Employee Status], Probation, [Employee Grade], [Grade Interval], [Home Base], [Job ID], [Job Title], [Org Code], [Organization Name], [Supervisor ID], [Supervisor Name], DayOfWork_JOIN, YEAR_OF_SERVICE, CAT_YOS FROM [D:\BI\SOURCE TEST BI\TEST.xlsx] (ooxml, embedded labels, table is POS_ASSIGNMENT); TQ Best Regards, Toni
fyi for this case, i already create calculation in expression and its work :
data January =Count(DISTINCT {<[Valid To]= {">=$(JAN)"}, [Valid From]={"<=$(FEB)"}>} [Emp No]) .
data February =Count(DISTINCT {<[Valid To]= {">=$(FEB)"}, [Valid From]={"<=$(MAR)"}>} [Emp No]) .
data March =Count(DISTINCT {<[Valid To]= {">=$(MAR)"}, [Valid From]={"<=$(APR)"}>} [Emp No]) .
I want explore this, maybe if can accommodate in edit script will help and simplify and made easy to average or any formula
TQ
Toni