Generate 1 raw data into separate raw data

[sutoniusahadong]

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

[sutoniusahadong]

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