Generate 1 raw data into separate raw data

sutoniusahadong — Mon, 04 Mar 2019 03:25:03 GMT

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

Re: Generate 1 raw data into separate raw data

sutoniusahadong — Mon, 04 Mar 2019 03:34:39 GMT

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

Toni

topic Generate 1 raw data into separate raw data in QlikView

Generate 1 raw data into separate raw data

Re: Generate 1 raw data into separate raw data