Solved: Re: Assign a date period based on 2 dates - Qlik Community

robin_heijt · ‎2019-01-16

Hi,

My QS app is currently build on approximately 10 different data files, which are linked together through joins.

The main identifier in these files are employees linking all different files together through a unique identifier number.

Each employee has a start and end date (In case of no end date, it is mapped as Today).

Based on these dates, I want to create a date field that shows in which Year/Month the employee was or is active.

With the help of @sunny_talwar I have been able to identify a solution. However with approximately 7000 Employees in my files, the following piece of code is taking more than 15 hours when joined in the general script eventually leading to the script running into a run-time error.

When adding the code as a separate entity, and linking it together with the Unique identifier, it takes only 1 hour and it succeeds.

The reason I need this code added to the joined script, is because I need to write an IF Statement that involves fields from different files.

I am now hoping somebody can help me figure out a way, how I can use this code with the fields from the joined script. So I can write the IF Statement with fields from different files.

Load*,

YearName(ActiveDate, 0, 7) as "ActiveCycle";

LOAD *,
  Date("Hiring Date" + IterNo() - 1) as ActiveDate
//   MonthName("Hiring Date" + IterNo() - 1) as ActiveMonthYear,
//  Year(Date("Hiring Date" + IterNo() - 1)) as ActiveYear
While "Hiring Date" + IterNo() - 1 <= Alt("Leaving Date", Today());
load*,
alt(Date(Date#([Termination Date], 'DD.MM.YYYY')), date(today())) AS [Leaving Date];

Load*,
if(S='3',date(today()),[Leaving Da]) as "Termination Date";

LOAD
	[Global ID],
	Date(Date#([Hiring Dat], 'DD.MM.YYYY') ) AS [Hiring Date],
    "Leaving Da",
    S
    
FROM [lib://Qlik_People_Reporting (one_c403515)/Employee Report.xlsx]
(ooxml, embedded labels, table is Sheet1);

Thanks!

mjamesking · ‎2019-01-25

Hi,

Busy day yesterday, but here we go.

The script below generates a calendar from 01 Jan 1980 to now, with a row for every date in between.

It then loads a file of 10,000 example employees with start dates and termination dates (some of which are blank) that fall in between the period that the calendar covers (randomly, but with the termination date after the start date).

It then uses the interval match function to create a table with a row for every employee for every date they were employed. This is then tidied up a bit (removing some empty rows) to create our Employed_Facts.

These link to the Employees table, on the Employee_ID.

It does generate a lot of rows, around 42 million, but there are a lot of date and employee combinations to be had, it takes about 30 seconds to run.

The code to generate the calendar isn't mine, it's been taken from another post on the community and I've tweaked it a little.

More info on how interval match works is here:

Interval Match

I've also attached the excel file that I created for the mocked up employee records. You'll need to create a connection to a Documents folder and put the file there, or if you already have one use that and change the path for the library to load the excel data from. Hope this all helps. As far as combining this with the rest of your script goes, it should be straight forward if you are using a data model that works using concatenated facts. PS I tend to use numbers for dates, so that I don't fall foul of date formatting issues in joins or expressions, it's an old habit.

Matt

// Calendar generation

Let vMinDate = 29221;
Let vMaxDate = Floor(Num(Today()));

Let vStartDate = YearStart(vMinDate)-1;
Let vEndDate = Floor(YearEnd(vMaxDate));

// --- The Calendar ---------------------------------------------------------------------------------------------
Calendar:
Load // --------------------------------------- Grain = Year -------------
Year as Year,
YearStart(Date) as YearStart,
If(Mod(Year,400)=0,1,If(Mod(Year,100)=0,0,If(Mod(Year,4)=0,1,0)))
as IsLeapYear,
// --------------------------------------- Grain = Quarter ----------
Dual('Q' & Quarter, Quarter) as Quarter,
QuarterStart(Date) as QuarterStart,
Dual(Year(Date) & ' Q' & Quarter, QuarterStart(Date))
as YearQuarter,
// --------------------------------------- Grain = Month ------------
Month as Month,
MonthStart(Date) as MonthStart,
Date(MonthStart(Date),'YYYY MMM') as YearMonth,
Mod(Month-1,3)+1 as MonthOfQuarter,
// --------------------------------------- Grain = Day --------------
Date - YearStart(Date) + 1 as DayOfYear,
Date - QuarterStart(Date) + 1 as DayOfQuarter,
Day(Date) as DayOfMonth,
Date,
Floor(Num(Date)) as DateNum ;
Load // ------------------------------------------------------------------
Year(Date) as Year,
Ceil(Month(Date)/3) as Quarter,
Month(Date) as Month,
Date;
Load // ------------------------------------------------------------------
Date($(vStartDate)+RecNo()) as Date
Autogenerate vEndDate - vStartDate ;

// end of calendar generation

// start of employee load

Employees:

LOAD
Employee_ID

// plus what other attributes you have about employees

FROM [lib://Documents/Employees.xlsx]
(ooxml, embedded labels, table is Sheet1);

Employed_Periods:

LOAD
Employee_ID,
"Start Date",
IF(LEN("Termination Date") = 0, FLOOR(NUM(Today())), "Termination Date") AS "End Date"
FROM [lib://Documents/Employees.xlsx]
(ooxml, embedded labels, table is Sheet1);

Inner Join IntervalMatch (DateNum)

LOAD "Start Date", "End Date"
RESIDENT Employed_Periods;

Employed_Facts:

NoConcatenate

LOAD *, 'Employed' AS Fact_Type
RESIDENT Employed_Periods
WHERE LEN("Start Date") <> 0;

DROP TABLE Employed_Periods;

// end of employee load

View solution in original post

mjamesking · ‎2019-01-23

Hi,

Are you stilling looking to do this? I’d suggest you need a fact table, and a generic calendar. Then add ‘employed’ facts to your table for each date a particular employee is employed, using the interval match function perhaps. Even with 7000 employees and a few decades of dates this should not be to hefty a join to produce the facts.

Let me know if you need any further assistance and I could mock something up

Matt

robin_heijt · ‎2019-01-23

Hi Matt,

Thanks for your response. It is definitely still an ongoing issue. It's bothering me for quite a while now.
Unfortunately I have a hard time understanding your idea. So if you would be so kind to help me out in this situation I would be very grateful.
If you need anymore information from my side, please let me know.

Thanks a lot!

Robin

mjamesking · ‎2019-01-23

No problem. I’ll send you an example tomorrow.

Happy to help

Matt

robin_heijt · ‎2019-01-24

Much appreciated, looking forward to hear from you!

mjamesking · ‎2019-01-25

Hi,

Busy day yesterday, but here we go.

The script below generates a calendar from 01 Jan 1980 to now, with a row for every date in between.

It then loads a file of 10,000 example employees with start dates and termination dates (some of which are blank) that fall in between the period that the calendar covers (randomly, but with the termination date after the start date).

It then uses the interval match function to create a table with a row for every employee for every date they were employed. This is then tidied up a bit (removing some empty rows) to create our Employed_Facts.

These link to the Employees table, on the Employee_ID.

It does generate a lot of rows, around 42 million, but there are a lot of date and employee combinations to be had, it takes about 30 seconds to run.

The code to generate the calendar isn't mine, it's been taken from another post on the community and I've tweaked it a little.

More info on how interval match works is here:

Interval Match

I've also attached the excel file that I created for the mocked up employee records. You'll need to create a connection to a Documents folder and put the file there, or if you already have one use that and change the path for the library to load the excel data from. Hope this all helps. As far as combining this with the rest of your script goes, it should be straight forward if you are using a data model that works using concatenated facts. PS I tend to use numbers for dates, so that I don't fall foul of date formatting issues in joins or expressions, it's an old habit.

Matt

// Calendar generation

Let vMinDate = 29221;
Let vMaxDate = Floor(Num(Today()));

Let vStartDate = YearStart(vMinDate)-1;
Let vEndDate = Floor(YearEnd(vMaxDate));

// --- The Calendar ---------------------------------------------------------------------------------------------
Calendar:
Load // --------------------------------------- Grain = Year -------------
Year as Year,
YearStart(Date) as YearStart,
If(Mod(Year,400)=0,1,If(Mod(Year,100)=0,0,If(Mod(Year,4)=0,1,0)))
as IsLeapYear,
// --------------------------------------- Grain = Quarter ----------
Dual('Q' & Quarter, Quarter) as Quarter,
QuarterStart(Date) as QuarterStart,
Dual(Year(Date) & ' Q' & Quarter, QuarterStart(Date))
as YearQuarter,
// --------------------------------------- Grain = Month ------------
Month as Month,
MonthStart(Date) as MonthStart,
Date(MonthStart(Date),'YYYY MMM') as YearMonth,
Mod(Month-1,3)+1 as MonthOfQuarter,
// --------------------------------------- Grain = Day --------------
Date - YearStart(Date) + 1 as DayOfYear,
Date - QuarterStart(Date) + 1 as DayOfQuarter,
Day(Date) as DayOfMonth,
Date,
Floor(Num(Date)) as DateNum ;
Load // ------------------------------------------------------------------
Year(Date) as Year,
Ceil(Month(Date)/3) as Quarter,
Month(Date) as Month,
Date;
Load // ------------------------------------------------------------------
Date($(vStartDate)+RecNo()) as Date
Autogenerate vEndDate - vStartDate ;

// end of calendar generation

// start of employee load

Employees:

LOAD
Employee_ID

// plus what other attributes you have about employees

FROM [lib://Documents/Employees.xlsx]
(ooxml, embedded labels, table is Sheet1);

Employed_Periods:

LOAD
Employee_ID,
"Start Date",
IF(LEN("Termination Date") = 0, FLOOR(NUM(Today())), "Termination Date") AS "End Date"
FROM [lib://Documents/Employees.xlsx]
(ooxml, embedded labels, table is Sheet1);

Inner Join IntervalMatch (DateNum)

LOAD "Start Date", "End Date"
RESIDENT Employed_Periods;

Employed_Facts:

NoConcatenate

LOAD *, 'Employed' AS Fact_Type
RESIDENT Employed_Periods
WHERE LEN("Start Date") <> 0;

DROP TABLE Employed_Periods;

// end of employee load

robin_heijt · ‎2019-01-29

This is amazing!

Thank you very much!

I am only still having some issues in joining the facts with my other tables.
Maybe you could guide me here?

How exactly do I include the "Year" & "Month" into my other scripts?
So I have:

Load
Employee data
From***
Employee_Periods:
***
From****
Load 
Employed_Facts:
NoConcatenate
LOAD *, 'Employed' AS Fact_Type
RESIDENT Employed_Periods
WHERE LEN("Start Date") <> 0;
DROP TABLE Employed_Periods;

Movements:
Load
effective Date,
Movement Type
From ***

Currently the table looks like this:

But What I need is this: (Have all lines for each employee, but associate the movement years to the active in year.) So when I would select Active year 2017 I want to see only the line with the 2017 movement and not the 2018 movement.

Thanks a lot for your help!

mjamesking · ‎2019-01-29

Hi,

So how I'd approach this is to have one Fact table, with the different types of facts stacked on top of one another, each with a Fact_Type. I'd then use Set Analysis in my expressions to ensure that I'm showing the correct values in each columns (the counts or sums, or whatever of the particular facts that you are after.

So Create a Master_Facts table, and load into all of the Facts from your Employed_Facts table, then drop the Employed_Facts table. Make sure you explicitly state NoConcatenate before the load statement so that it does what you want, otherwise the new Master_Facts table may not be created if the columns are exactly the same as the existing Employed_Facts table.

Then add in your Movement Facts, possibly in two different fact types (Promotion and Lateral Move), ensuring you include the ID of the employee, and the date that the Promotion or Move occurred. Using the same columns as you did for the Employed_Facts... and make sure the data you are putting in the Date column is in the same format that you have previously used (and it also matches that of the Date column in your calendar).

So you'll end up with three tables, one of employees, one of different Facts with types with the dates they occurred and one being the calendar.

You may even want to go as far as recording a move out and move in fact, and then give the fact a 'Department' key, and also link your fact table to a department table. If that makes sense.

Try to keep the number of tables to a minimum, based upon what it is you want to achieve. If all you are after is things to do with employees over time, you just need three tables at the end of the script. One for things (facts) one for emploees, and one for time (your calendar).

Again, hope this helps.

If you want me to add to my mocked up script to include movement, then I'm happy to do so when I get chance.

Cheers

Matt

robin_heijt · ‎2019-01-29

Hi Matt,

Thanks for your elaborate response, and i can see that you clearly have a lot of knowledge about Qlik Sense.

However I am having a hard time understanding what you were trying to explain to me (Greatly appreciated by the way)

So if you would be able to help me out with a mock up, that would be hugely appreciated.

To give a bit of more insight to what I am working with please see the below code. I cut out a bunch of tables to cut down on info, but essentially I have a bunch of tables combined, getting it loaded in 1 final table.

What I need is to have the previously mentioned table result to have the movements mapped accordingly to the active cycle.

If you could mock up something on how I would do that in this script, I would be very thankful.

Regards,

Robin

//================================================================================================================
//													Employee Report - See Section
//================================================================================================================
Employee_Report:
LOAD
    "Global ID",
  "Personal ID",
   "Original hire date",
    "Leaving Date",
    "Position Start Date Future"
Resident FinalEMReport;
Drop Table FinalEMReport;

//================================================================================================================
//													Frequent Traveler Country
//================================================================================================================
Outer Join (Employee_Report)
LOAD
    "Global ID",
    "HOST
Country"  as "Host Country",
    "Assignment Type"
FROM [lib://Qlik_People_Reporting (one_c403515)/Mobility Report.xlsx]
(ooxml, embedded labels, table is [Raw Data]);

//================================================================================================================
//													Movement Report - See Section
//================================================================================================================

     
outer join (Employee_Report)
 Movement:
Load 
// "Global ID",
"Personal ID",
"HR Administrator" as "People Business Partner",
     "Name Movement",
"Employee Group Movement",
"Date of the Movement",
     "Action type",
     "Action Reason",
     "Band Before",
"Position ID Before",
"Position Before",
"Function Before",
"Band After",
"Position ID After",
"Position After",
"Function After",
"In scope for Turnover?",
"TO Filter",
    "Effective Date",
    "Movement Type"
    
     Resident Movements
     where "TO Filter"='In Scope' or isnull("TO Filter") and "Movement Exclusion"='In Scope' and len("Effective Date">0);
     Drop table Movements;
 
Outer Join (Employee_Report)
//================================================================================================================
//													Local Entity
//================================================================================================================
 Local_Entity:
 LOAD
     ObjectID AS "Position ID",
     "Local Entity",
     "Local Entity Description",
     "Local Entity L1",
     "Local Entity Description1",
     "Local Entity L2",
     "Local Entity Description2",
     "Local Entity L3",
     "Local Entity Description3",
     "Local Entity L4",
     "Local Entity Description4",
     "Local Entity L5",
     "Local Entity Description5",
     "Local Entity L6",
     "Local Entity Description6"
 FROM [lib://Qlik_People_Reporting (one_c403515)/Local Entity.XLSX]
 (ooxml, embedded labels, table is Sheet1);
 
 
  Outer Join (Employee_Report)
//================================================================================================================
//													Macro Entity
//================================================================================================================  
  Macro_Entity:
LOAD
    ObjectID AS "Position ID",
   "Macro Organizational Entity",
   "Macro Entity Description",
     "Macro Entity Type",
     "Mac.Org. Ent. L1",
   "Macro Entity Description1",
     "Mac.Org. Ent. L2",
   "Macro Entity Description2",
  "Mac.Org. Ent. L3",
  "Macro Entity Description3",
     "Mac.Org. Ent. L4",
   "Macro Entity Description4",
     "Mac.Org. Ent. L5",
   "Macro Entity Description5",
     "Mac.Org. Ent. L6",
     "Macro Entity Description6"
FROM [lib://Qlik_People_Reporting (one_c403515)/Macro Entity.XLSX]
(ooxml, embedded labels, table is Sheet1);

  Outer Join (Employee_Report)
  

//================================================================================================================
//											Final Table
//================================================================================================================


 Final:
   NoConcatenate
   
   
//================================================================================================================
//													Adherence per Cycle Mapping
//================================================================================================================  
  
Load*,
 if ("Cycle" ='2018-2019', "Adherence 2018",
 if ("Cycle" ='2017-2018',"Adherence 2017",
 if ("Cycle" ='2016-2017',"Adherence 2016" )))as Adherence;

//================================================================================================================
//													OPR per Cycle Mapping
//================================================================================================================
  Load*,
 if ("PC Cycle" ='2018/2019',"OPR 2018",
 if ("PC Cycle" ='2017/2018',"OPR 2017",
 if ("PC Cycle" ='2016/2017',"OPR 2016" )))as OPR;
//================================================================================================================
//													Adherence Mapping
//================================================================================================================
    Load*, 
    		if ("Cycle" <>'2018-2019', null(),
           	 If(IsNull("OPR 2018"), (Null()), 
       	   	 if(IsNull("Movement Type"), (Null()),
            if("OPR 2018" ='Not Rated', (Null()),
          	 If("OPR 2018" ='1B' and "Movement Type" ='Termination Involuntary', ('Adherent'),
             If("OPR 2018" ='1B' and "Movement Type" ='Termination Voluntary', ('Adherent'),
          	 If("OPR 2018" ='1A' and "Movement Type" ='Termination Involuntary' , ('Adherent'),
             If("OPR 2018" ='1A' and "Movement Type" ='Termination Voluntary' , ('Adherent'),
             If("OPR 2018" ='1A' and "Movement Type" ='Lateral Move', ('Adherent'),
         	 If("OPR 2018" ='2' and "Movement Type" ='Lateral Move'  , ('Adherent'),
             If("OPR 2018" ='2' and "Movement Type" ='Promotion Within Band', ('Adherent'), 
             If("OPR 2018" ='4A' and "Movement Type" ='Promotion Band Up', ('Adherent'),
          	 If("OPR 2018" ='3B' and "Movement Type" ='Lateral Move'  , ('Adherent'),
             If("OPR 2018" ='3B' and "Movement Type" ='Promotion Within Band', ('Adherent'),
           	 If("OPR 2018" ='3A' and "Movement Type" ='Lateral Move', ('Adherent'),
             If("OPR 2018" ='3A' and "Movement Type" ='Promotion Within Band', ('Adherent'), 
             If("OPR 2018" ='3A' and "Movement Type" ='Promotion Band Up', ('Adherent'),
           	 If("OPR 2018" ='4B' and "Movement Type" ='Lateral Move' , ('Adherent'), 
             If("OPR 2018" ='4B' and "Movement Type" ='Promotion Within Band', ('Adherent'), 
             If("OPR 2018" ='4B' and "Movement Type" ='Promotion Band Up', ('Adherent'), ('Non-Adherent'))))))))))))))))))))) as "Adherence 2018";
     
    
//================================================================================================================
//													PC Cycle Mapping & Movement Cycle Mapping
//================================================================================================================   
Load*,
if(isnull("Cycle") and "Employment Status"='Active', '2018/2019',
if("Cycle"='2018-2019', '2018/2019',
if("Cycle"='2017-2018', '2017/2018',
if("Cycle"='2016-2017', '2016/2017',
if("Cycle"='2015-2016', '2015/2016'
))))) as "PC Cycle";

//Load*,
//if("Cycle"='2018-2019' or isnull("Cycle") and ActiveCycle='2018-2019', '2018/2019',
//if("Cycle"='2017-2018' or isnull("Cycle") and ActiveCycle='2017-2018', '2017/2018',
//if("Cycle"='2016-2017' or isnull("Cycle") and ActiveCycle='2016-2017', '2016/2017',
//if("Cycle"='2015-2016' or isnull("Cycle") and ActiveCycle='2015-2016', '2015/2016'
//)))) as "PC Cycle";

//Load *,
//YearName(Date, 0, 7) as "ActiveCycle";


Load *, 
YearName("Effective Date", 0, 7) as "Cycle"
;

//================================================================================================================
//													Time in Company Mapping
//================================================================================================================ 
Load*,
if("Time in Company Year Number"<1, 'less than 1 Year',
if("Time in Company Year Number">=1 and "Time in Company Year Number" <5, '1-5 Years',
if("Time in Company Year Number">=5 and "Time in Company Year Number" <10, '5-10 Years',
if("Time in Company Year Number">=10 and "Time in Company Year Number" <15, '10-15 Years',
if("Time in Company Year Number">=15 and "Time in Company Year Number" <20, '15-20 Years',
if("Time in Company Year Number">=20 and "Time in Company Year Number" <25, '20-25 Years',
if("Time in Company Year Number">=25 and "Time in Company Year Number" <30, '25-30 Years',
if("Time in Company Year Number">=30 and "Time in Company Year Number" <40, '30-40 Years',
if("Time in Company Year Number">=40, '40+ Years'))))))))) as "Time in Company Group";
Load*,
if(isnull("Original hire date"), null(),
Year(Today() - "Original hire date" + 1) - 1900 & ' Years, '
& Num(Month(Today() - "Original hire date" + 1) - 1) & ' Months')as "Time in Company";

Load*,
Year(Today() - "Original hire date" + 1) - 1900 & ','
& Num(Month(Today() - "Original hire date" + 1) - 1) & ''as "Time in Company Number"
;

Load*,
Year(Today() - "Original hire date" + 1) - 1900 as "Time in Company Year Number"
;

//================================================================================================================
//													Final Report
//================================================================================================================
 Load  
"Global ID",
"Global ID" as  "Global ID OPR",
"Personal ID",
If(Len(Trim([Name])) > 0, [Name], [Name Movement]) as Name,
Gender,
"Country of Birth Initials",
"Country of Birth",
"Nationality",
"Language key",
If("Assignment Type"='Frequent Traveler',"Host Country", Country) as Country,
"EBM Level",
"LM",
"LM Global ID",
"LM Personal ID",
"LM Position",
// "Hiring date",
"Original hire date",
"Date of birth",
if("Position ID"=99999999,"Position ID Before","Position ID") as "Position ID",
"Position",
"Bandpreclean",
"Band Postion",
"Grandfathered Band",
"Grandfathering Start Date",
"Grandfathering End Date",
"Employee Group ID",
"Employee Group",
"AB-InBev Entity ID",
"AB-InBev Entity Text",
"Macro Organizational Entity ID",
Mac.Org.En1,
// "Leaving Date",
"Employee Status ID",
"Employment Status",
"Employee subgroup ID",
"Employee subgroup",
Org.unit,
"Contract Type ID",
"Contract Type",
"Cost Center",
Expat,
"Home Country",
// "Host Country",
"Maritial Status",
Children,
"Position Start Date Future",
"Name Movement",
"Employee Group Movement",    
"Action type",
"Action Reason",
"Band Before",
"Position ID Before",
"Position Before",
"Function Before",
"Band After",
"Position ID After",
"Position After",
"Function After",
If(isnull("OPR 2015"),'Not Rated',"OPR 2015") as "OPR 2015",
If(isnull("OPR 2016"),'Not Rated',"OPR 2016") as "OPR 2016",
If(isnull("OPR 2017"),'Not Rated',"OPR 2017") as "OPR 2017",
If(isnull("OPR 2018"),'Not Rated',"OPR 2018") as "OPR 2018",
"Local Entity",
"Local Entity Description",
"Local Entity L1",
"Local Entity Description1",
"Local Entity L2",
"Local Entity Description2",
"Local Entity L3",
"Local Entity Description3",
"Local Entity L4",
"Local Entity Description4",
"Local Entity L5",
"Local Entity Description5",
"Local Entity L6",
"Local Entity Description6",
"Macro Organizational Entity",
if(isnull("Macro Entity Description"),"Macro entity text","Macro Entity Description") as "Macro Entity Description" ,
"Macro Entity Type",
"Mac.Org. Ent. L1",
"Macro Entity Description1",
"Mac.Org. Ent. L2",
"Macro Entity Description2",
"Mac.Org. Ent. L3",
"Macro Entity Description3",
"Macro Entity Description3" as "BU",
"Mac.Org. Ent. L4",
"Macro Entity Description4",
"Mac.Org. Ent. L5",
"Macro Entity Description5",
"Mac.Org. Ent. L6",
"Macro Entity Description6",
"Position Start Date",
"People Director Global ID",
"People Director OM",
"Position Type",
"Talent Pool 2018",
"Talent Pool 2017",
"Talent Pool 2016",
"Talent Pool 2015",
"Talent Pool 2014",
"Talent Pool 2013",
"TP 2012",
"TP 2011",
"TP 2010",
"TP 2009",
"High Fliers",
Talent?,
"Trainee + Year",
"Trainee Group",
"Trainee year",
"Career Speed",
"Carrer Speed GMBAs",
"Name TO",
"Country Key TO",
"Company Code TO",
"Termination action",
"Termination reason",
"Termination Date TO",
"Last day at work",
"Band TO",
"Employee Group TO",
"AB InBev entity code text Level3",
"AB InBev entity code text Level4",
"Last Position ID TO",
"Contract type TO",
"Birth date TO",
"Nationality TO",
"In scope for Turnover?",
Plant,
"Leadership Appraisal",
"Functional Appraisal",
//Belt,
//"Completed Classroom Trainings",
//"Completed Online Learnings",
"Country Key for VP-1",
"EBM Level Sub",
"Target Achievement",
"Manager Effectiveness",
"Engagement Score",
"Compa Ratio",
Referals,
"Total Attendence Events",
"Open to Move Into",
"Aspiration - Timing",
"Aspiration - Comments",
"Aspiration - Modified Date",
"Aspiration - Identified By",
"More About Me",
"Key Strengths",
Mobility,
"Mobility Comments",
Languages,
"Business E-mail",
"Education and Skills",
"Preliminary OPR Rating",
"Talent Pool Nomination",
"Work History",
"Manager Comments",
"TO Filter",
"Company Code",
"People Business Partner",
"Referral Area of Interest",
"Referral Name",
"Current Step Referral",
"Current Status Referral",
    "Referrer Name",
        "Effective Date",
    "Movement Type",
    "Host Country",
    "Assignment Type"
//Year,
//Month



Resident Employee_Report
Where len("Global ID">0);

//================================================================================================================
//													Movement Number Mapping
//================================================================================================================
        Actions_OPR:     
 LOAD *,

      AutoNumber("Effective Date", "Global ID" & Cycle) as "Movement Number"

 RESIDENT Final
 Order by "Effective Date";
Drop Table Final;

mjamesking · ‎2019-01-30

Hi,

I've added another tab to the spreadsheet I used, with some dummy movements in (attached).

I've tweaked the script that loads the Employees as well, only a little. So we have a table of employees, with some employee specific data (start date, termination date), a table of facts, now with three fact types (Employed, Lateral Move, and Promotion Band Up).

To get the sort of view that you are after I would create a table with

2 dimensions: Employee_ID and Year

and 3 expressions

Employed: =COUNT({<Fact_Type = {'Employed'}>} DISTINCT Employee_ID)

Lateral Move: =COUNT({<Fact_Type = {'Lateral Move'}>} Employee_ID)

Promotion: =COUNT({<Fact_Type = {'Promotion Band Up'}>} Employee_ID)

The script takes a very simple approach, and doesn't load a lot of fields, but it's how I would approach this. Keep it simple to begin with, making sure you fully understand the data model you are building, get something that works and then start to enhance it (with departments, levels etc...)

Hope this helps.

Matt

Script follows:

// Calendar generation

Let vMinDate = 29221;
Let vMaxDate = Floor(Num(Today()));

Let vStartDate = YearStart(vMinDate)-1;
Let vEndDate = Floor(YearEnd(vMaxDate));

// --- The Calendar ---------------------------------------------------------------------------------------------
Calendar:
Load // --------------------------------------- Grain = Year -------------
Year as Year,
YearStart(Date) as YearStart,
If(Mod(Year,400)=0,1,If(Mod(Year,100)=0,0,If(Mod(Year,4)=0,1,0)))
as IsLeapYear,
// --------------------------------------- Grain = Quarter ----------
Dual('Q' & Quarter, Quarter) as Quarter,
QuarterStart(Date) as QuarterStart,
Dual(Year(Date) & ' Q' & Quarter, QuarterStart(Date))
as YearQuarter,
// --------------------------------------- Grain = Month ------------
Month as Month,
MonthStart(Date) as MonthStart,
Date(MonthStart(Date),'YYYY MMM') as YearMonth,
Mod(Month-1,3)+1 as MonthOfQuarter,
// --------------------------------------- Grain = Day --------------
Date - YearStart(Date) + 1 as DayOfYear,
Date - QuarterStart(Date) + 1 as DayOfQuarter,
Day(Date) as DayOfMonth,
Date,
Floor(Num(Date)) as DateNum ;
Load // ------------------------------------------------------------------
Year(Date) as Year,
Ceil(Month(Date)/3) as Quarter,
Month(Date) as Month,
Date;
Load // ------------------------------------------------------------------
Date($(vStartDate)+RecNo()) as Date
Autogenerate vEndDate - vStartDate ;

// end of calendar generation

// Load employee info

Employees:

LOAD
Employee_ID,
"Start Date",
"Termination Date"

// plus what other attributes you have about employees

FROM [lib://Documents/Employees.xlsx]
(ooxml, embedded labels, table is Sheet1);

// Work out employed periods

Employed_Periods:

LOAD
Employee_ID,
"Start Date",
IF(LEN("Termination Date") = 0, FLOOR(NUM(Today())), "Termination Date") AS "End Date"
FROM [lib://Documents/Employees.xlsx]
(ooxml, embedded labels, table is Sheet1);

Inner Join IntervalMatch (DateNum)

LOAD "Start Date", "End Date"
RESIDENT Employed_Periods;

// Build facts table

Facts:

NoConcatenate

LOAD Employee_ID,
DateNum,
'Employed' AS Fact_Type
RESIDENT Employed_Periods
WHERE LEN("Start Date") <> 0;

DROP TABLE Employed_Periods;

// Load movements

Movements:

LOAD
Employee_ID,
Movement_Type,
"Movement Date"
FROM [lib://Documents/Employees.xlsx]
(ooxml, embedded labels, table is Sheet2);

// Add movement facts to facts table and drop movements

Concatenate (Facts)

LOAD Movement_Type AS Fact_Type,
Employee_ID,
"Movement Date" AS DateNum
Resident Movements;

DROP TABLE Movements;

// end