Time since start

[robin_heijt]

Hi,

I have this data for every person:

      ID           Name           Scale          Start Date

1000069 John Doe      

1000069 John Doe      

1000069 John Doe                          1/1/2006

1000069 John Doe                          8/1/2006

1000069 John Doe      03                1/26/2007

1000069 John Doe      GW              5/1/2007

1000069 John Doe      GW              1/1/2008

1000069 John Doe      BWG 07       10/1/2008

1000069 John Doe      BWG 07       12/1/2009

1000069 John Doe      BWG 07       3/1/2011

1000069 John Doe      BWG 07       10/1/2011

1000069 John Doe      BWG 07       1/1/2012

1000069 John Doe      BWG 07       1/1/2013

1000069 John Doe      BWG 07-V      1/1/2014

1000069 John Doe      BWG 08-V      10/1/2014

1000069 John Doe      BWG 08-V      1/1/2015

1000069 John Doe      BWG 08-V      1/1/2016

1000069 John Doe      BWG 08-V      1/1/2017

1000069 John Doe      BWG 08-V      10/1/2017

1000069 John Doe      BWG 08           1/1/2018

1000069 John Doe      BWG 08            2/1/2018

I need to create a formula within the data load editor that does the following:

1. Check for the latest start date

2. Make sure that the start date represents the unified start date based on the first time the person entered the scale. E.g. the highlighted data represents the latest data. However I do not want to look at the last entry, as this represents the same scale. So in this case the latest date should return 1/1/2018 as the scale is the same.

3. Then based on this latest start date I want to apply this formula:

// Time in Scale
Year(Today() - "Start date" + 1) - 1900 & ' Years, '
& Num(Month(Today() - "Start hire date" + 1) - 1) & ' Months, '
& Day(Today() - "Start hire date" + 1) & ' Days' as "Time in Scale",

in order to calculate the time in the scale.

Can anybody help me achieve this within the data load editor?

Thanks

[dplr-rn]

It should work with your table too as I am grouping by name and id. Use your table instead of temp

[dplr-rn]

Added a new ID and tested.

Slight change in the Temp3 load

Temp3:

Load *,

//if(ApplyMap('ScaleMap',AutoNumber(ID&Name&Scale),'')='','',if(previous(Scale)=Scale,'', [Start date])) as ScaleStart

if((Previous(ID)=ID and Previous(Scale)=Scale),'', [Start date]) as ScaleStart

resident Temp

;

rest remains the same and i achieve below

ID	Name	Scale	Start date	ScaleStartFinal
1000069	John Doe	BWG 07-V	1/1/2014
1000069	John Doe	BWG 08-V	10/1/2014
1000069	John Doe	BWG 08-V	1/1/2015
1000069	John Doe	BWG 08-V	1/1/2016
1000069	John Doe	BWG 08-V	1/1/2017
1000069	John Doe	BWG 08-V	10/1/2017
1000069	John Doe	BWG 08	1/1/2018	1/1/2018
1000069	John Doe	BWG 08	2/1/2018
1000070	Jack Doe	BWG 08-V	10/1/2017
1000070	Jack Doe	BWG 08	1/1/2018	1/1/2018
1000070	Jack Doe	BWG 08	2/1/2018

[robin_heijt]

Hi Dilip,

First off I would like to thank you for all the effort your putting into helping me, it is much appreciated.

Secondly, I think we're almost there, as this is the result now:

Problem:

1. I get duplicates, every person has 1 line that just shows the empty time in band.

When I add ScaleStartFinal to the table, I see that there is always 1 line with a number, and 1 blank line. Causing duplicates in the lines with the blanks.

This is my load script right now:

Banding:  
  LOAD
      PERSONID_EXT as "Global ID",
      Pers.No.,
      "Personnel Number" as "Name Banding",
  //     "Employment Status",
  //     "Company Code",
  //     Position,
  //     Position1,
      "PS group",
      "Start Date" as "Band Start Date",
      "End Date" as "Band End Date"
  FROM
  (ooxml, embedded labels, table is Sheet1);

Temp3:
Load *,
//if(ApplyMap('ScaleMap',AutoNumber(ID&Name&Scale),'')='','',if(previous(Scale)=Scale,'', [Start date])) as ScaleStart
if((Previous("Global ID")="Global ID" and Previous("PS group")="PS group"),'', "Band Start Date") as ScaleStart
resident Banding
;

ScaleMap2:
mapping load
AutoNumber("Global ID"&"Name Banding"), Max(ScaleStart) as MaxScaleStart
resident Temp3
group by "Global ID", "Name Banding"
;

Final:
Load*, // Time in Band
 Year(Today() - ScaleStartFinal + 1) - 1900 & ' Years, '
 & Num(Month(Today() - ScaleStartFinal + 1) - 1) & ' Months, '
 & Day(Today() - ScaleStartFinal + 1) & ' Days' as "Time in Band";

Load *,
if(ApplyMap('ScaleMap2',AutoNumber("Global ID"&"Name Banding"),'')="Band Start Date","Band Start Date",'') as ScaleStartFinal
resident Banding
;

drop table Banding;
drop table Temp3;

Would you be able to advise?

Thank you so much.