Active in year based on dates

[robin_heijt]

Hi,

Using the following info

ID / Name / Start Date    / End Date

1 / John    / 01-01-2016 /   01-05-2017

2 / Jack     / 25-12-2015  /   -

3 / Maria  / 10-10-2017   / 05-05-2018

I would like to create a dimension that will allow me to filter on a year and see if a person was active in said year.

So John would be part of= 2016 & 2017

Jack would be part of 2015, 2016, 2017 & 2018

Maria would be part of 2017 & 2018

Can anyone help me create a dimension like this in the data load editor, to filter for people who were active in a chosen year. It would be great if I can create an even more granular dimension that would also allow me to deep dive in said months of the year.

Thanks

[sunny_talwar]

Try this script

Table:
LOAD ID,
	 Name,
	 Date([Start Date] + IterNo() - 1) as ActiveDate,
	 Year(Date([Start Date] + IterNo() - 1)) as ActiveYear
While [Start Date] + IterNo() - 1 <= Alt([End Date], Today());
LOAD * INLINE [
    ID, Name, Start Date, End Date
    1, John, 01-01-2016, 01-05-2017
    2, Jack, 25-12-2015, 
    3, Maria, 10-10-2017, 05-05-2018
];

It will create all the dates between your start and end date and will give you something like this

[robin_heijt]

Thanks a lot, it indeed works!

Now I have a hard time adapting this script into my main load, which is presented like this:

Load 
"Global ID",
"Personal ID",
If(Len(Trim([Name])) > 0, [Name], [Name Movement]) as Name,
Gender,
"Country of Birth Initials",
"Country of Birth",
"Nationality",
"Language key,
"Start Date",
"End Date"

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

Where would I place this script of yours?

Also would you know if this piece of code can be adapted to also create a filter that will allow me to see how many people are active in each month of the year?

Thanks a lot!

[sunny_talwar]

Like this

LOAD *,
  Date([Start Date] + IterNo() - 1) as ActiveDate,
  MonthName([Start Date] + IterNo() - 1) as ActiveMonthYear,
 Year(Date([Start Date] + IterNo() - 1)) as ActiveYear;
LOAD "Global ID",
  "Personal ID",
  If(Len(Trim([Name])) > 0, [Name], [Name Movement]) as Name,
  Gender,
  "Country of Birth Initials",
  "Country of Birth",
  "Nationality",
  "Language key,
  "Start Date",
  "End Date"
Resident Employee_Report
Where Len("Global ID">0);

---

@robin_heijt wrote:

Also would you know if this piece of code can be adapted to also create a filter that will allow me to see how many people are active in each month of the year?

Thanks a lot!

---

You can just use ActiveMonthYear as dimension and Count(DISTINCT Name) as expression to find the people who existed.
 

[robin_heijt]

Thanks,

Unfortunately the fields will now not show any values.

Might this be because the while statement is not included, which looks for the end date?

Thanks

[sunny_talwar]

Yes, you are right... forgot to add the while statement...

[sunny_talwar · Accepted solution]

Try this

LOAD *,
  Date([Start Date] + IterNo() - 1) as ActiveDate,
  MonthName([Start Date] + IterNo() - 1) as ActiveMonthYear,
 Year(Date([Start Date] + IterNo() - 1)) as ActiveYear
While [Start Date] + IterNo() - 1 <= Alt([End Date], Today());
LOAD "Global ID",
  "Personal ID",
  If(Len(Trim([Name])) > 0, [Name], [Name Movement]) as Name,
  Gender,
  "Country of Birth Initials",
  "Country of Birth",
  "Nationality",
  "Language key,
  "Start Date",
  "End Date"
Resident Employee_Report
Where Len("Global ID">0);

[robin_heijt]

Thanks a lot. It indeed seems to work exactly like I want it to.

Unfortunately I have only been able to make it work in a test app.

However when I apply the code to my real load script, all fields from 70% of my entire load turn up as null values.

I will attach a mock up of how my real load script looks like. You would do me a huge favor if you can see what might be going wrong with my real load script. 

Your piece of code is located at the bottom.

Employee_Report:
////////////////////////////////////////////////////// General Load Script/////////////////////////////////////////////

LOAD distinct
    "Global ID",
  "Personal ID",
    "Name",
    "Hiring date",
    "Company Code",
   "Leaving Date",


Resident FinalEMReport;
Drop Table FinalEMReport;

/////////////////////////////////////////////////////Movement/////////////////////////////////////////////

     
Concatenate (Employee_Report)
 Movement:
Load distinct
"Personal ID",
     "Name Movement",
"Employee Group Movement",
"Date of the Movement"
     Resident Movements
     where "TO Filter"='In Scope' or isnull("TO Filter") and "Movement Exclusion"='In Scope';
     Drop table Movements;
 
 Left Join(Employee_Report)
 
/////////////////////////////////////////////////////Navigate/////////////////////////////////////////////


 OPR_2018:
 
 LOAD distinct
     "Employee Global ID" as "Global ID",
     Keepchar("Preliminary OPR Rating",'4A4B3A3B21A1B') as "Preliminary OPR Rating",
     Keepchar("Last Published OPR Rating",'4A4B3A3B21A1B') as "OPR 2018"

 FROM [lib://People Reporting (eeinterbrew_heijtrob)/OPR 2018.xlsx]
 (ooxml, embedded labels, table is [Sheet1]);
 
   Left Join(Employee_Report)
 
 
 OPR_2017:
 
 LOAD distinct
     "Employee Global ID" as "Global ID",
 //     "Manager Employee ID",
 //     Manager,
 //     "Manager Comments",
//  "Year",
     "OPR Rating Scale" as "OPR 2017"
 FROM [lib://People Reporting (eeinterbrew_heijtrob)/OPR 2017.xlsx]
 (ooxml, embedded labels, table is [Sheet1]);
 
 
   Left Join(Employee_Report)
 
 
 OPR_2016:
 
 LOAD distinct
     "Employee Global ID" as "Global ID",
 //     "Manager Employee ID",
 //     Manager,
 //     "Manager Comments",
//  "Year",
     "OPR Rating Scale" as "OPR 2016"
 FROM [lib://People Reporting (eeinterbrew_heijtrob)/OPR 2016.xlsx]
 (ooxml, embedded labels, table is [Sheet1]);
 
 
   Left Join(Employee_Report)
 
 
 OPR_2015:
 
 LOAD distinct
 
     "Employee Global ID" as "Global ID",
 //     "Manager Employee ID",
 //     Manager,
 //     "Manager Comments",
//  "Year",
     "OPR Rating Scale" as "OPR 2015"
 FROM [lib://People Reporting (eeinterbrew_heijtrob)/OPR 2015.xlsx]
 (ooxml, embedded labels, table is [Sheet1]);
 
 Left Join (Employee_Report)
 
 LOAD distinct
    "Employee Global ID" as "Global ID",
//     Name
FROM [lib://People Reporting (eeinterbrew_heijtrob)/Navigate Report.xlsx]
(ooxml, embedded labels, table is Sheet1);
Outer Join (Employee_Report)
/////////////////////////////////////////////////////Macro & Local Entity/////////////////////////////////////////////

 Local_Entity:
 LOAD distinct
     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://People Reporting (eeinterbrew_heijtrob)/Local Entity.XLSX]
 (ooxml, embedded labels, table is Sheet1);
 
  Outer Join (Employee_Report)
  
  Macro_Entity:
LOAD distinct
    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://People Reporting (eeinterbrew_heijtrob)/Macro Entity.XLSX]
(ooxml, embedded labels, table is Sheet1);

  Outer Join (Employee_Report)
  
/////////////////////////////////////////////////////OM Report/////////////////////////////////////////////

LOAD distinct
"Global ID",
"Position Start Date"

Resident FinalPosition;
Drop Table FinalPosition;


  Outer Join (Employee_Report)
  
  LOAD distinct
    "Position ID" AS "Position ID",
    "Senior BP-Z16_Per. Nr" AS "People Director Global ID",
    "Senior BP-Z16_Name" AS "People Director OM",
    "Position Type_IT9015" AS "Position Type"
FROM [lib://People Reporting (eeinterbrew_heijtrob)/OM report.xlsx]
(ooxml, embedded labels, table is [Sheet1]);


  Outer Join (Employee_Report)
  
/////////////////////////////////////////////////////Talent Scorecard/////////////////////////////////////////////

  
LOAD distinct
	"Global ID"
FROM [lib://People Reporting (eeinterbrew_heijtrob)/Talent Scorecard.xlsx]
(ooxml, embedded labels, table is [Sheet1]);  
   
  
Outer Join(Employee_Report)

/////////////////////////////////////////////////////TO Data/////////////////////////////////////////////


LOAD distinct
    "Global ID"
FROM [lib://People Reporting (eeinterbrew_heijtrob)/Turnover Raw data.xlsx]
(ooxml, embedded labels, table is [Sheet1]);
  

 outer join (Employee_Report)
 
// LOAD
//     "Action Reason Description" as "Action Reason",
//     "In scope for Turnover?"
// FROM [lib://People Reporting (eeinterbrew_heijtrob)/Movement Exclusions.xlsx]
// (ooxml, embedded labels, table is [Sheet1]);

// outer join (Employee_Report)

/////////////////////////////////////////////////////Brewery Info/////////////////////////////////////////////


Plant:

LOAD distinct
    "Cost Ctr" as "Cost Center",
     Plant

FROM [lib://People Reporting (eeinterbrew_heijtrob)/Brewery Info.xlsx]
(ooxml, embedded labels, table is Sheet1);

/////////////////////////////////////////////////////LMS Info/////////////////////////////////////////////


outer Join (Employee_Report)
LOAD distinct
    "Global ID",
    Belt,
    "Completed Classroom Trainings",
    "Completed Online Learnings"
FROM [lib://People Reporting (eeinterbrew_heijtrob)/Belt & Training Report.xlsx]
(ooxml, embedded labels, table is Sheet1);

outer Join (Employee_Report)

////////////////////////////////////////////////////Recruitment/////////////////////////////////////////////


LOAD distinct
    "Global ID"
FROM [lib://People Reporting (eeinterbrew_heijtrob)/Recruitment Events.xlsx]
(ooxml, embedded labels, table is [Sheet1]);

Outer Join (Employee_Report)

LOAD distinct
    "Global ID"
FROM [lib://People Reporting (eeinterbrew_heijtrob)/Scores.xlsx]
(ooxml, embedded labels, table is Sheet1);








 Final:
   NoConcatenate
 

  
  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 Distinct 
  "Global ID",
    "Global ID" as  "Global ID OPR",
"Personal ID",
If(Len(Trim([Name])) > 0, [Name], [Name Movement]) as Name,
"Hiring date",
"Leaving Date",



Resident Employee_Report;
Drop table Employee_Report;

        Actions_OPR:     
 LOAD *,

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

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

Appreciate your help.

[sunny_talwar]

You have a lot many joins in your table + 1 concatenate... the first thing to know is that the table concatenated doesn't have Hiring Date and Leaving date

Concatenate (Employee_Report)
 Movement:
Load distinct
"Personal ID",
     "Name Movement",
"Employee Group Movement",
"Date of the Movement"
     Resident Movements
     where "TO Filter"='In Scope' or isnull("TO Filter") and "Movement Exclusion"='In Scope';
     Drop table Movements;

So, right away we know that this won't be expanded based on Hiring and Leaving Date.

Now the next thing to check is how does your table look after the join. Make sure everything is populated with Hiring and Leaving Dates. The main thing to check is that before you perform the while function... does every single row in your table have a Hiring and Leaving date or not. If they don't it may not populate the data properly.

Does that make sense?

[robin_heijt]

Thanks. I have been able to work it out. And it partially works now.

The only thing I am missing is the Active dates for the people that are still active. As these people have a blank line in the report for "Leaving Date"

I tried solving this with the following:

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
	[Global ID],
	[Pers.No.],
	[First name],
	Date(Date#([Hiring Dat], 'DD.MM.YYYY') ) AS [Hiring Date],
Date(if(len([Leaving Da])<1,date(today()),Date(Date#([Leaving Da], 'DD.MM.YYYY')))) AS [Leaving Date]

Which does return today's date in the table. However the "While" statement does not seem to pick up on this date, as you can see in this picture.

Can you tell me what I might be doing wrong here?