topic Re: Time since start in App Development

Time since start

robin_heijt — Mon, 01 Oct 2018 19:11:25 GMT

Hi,

I have this data for every person:

ID Name Scale Start Date

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

Re: Time since start

dplr-rn — Mon, 01 Oct 2018 19:45:33 GMT

Not sure if there is a more elegant way to achieve this but below should work

Temp2:

Load

ID,

Name,

Scale,

Date(min([Start Date]),'M/D/YYYY') as MinStart

resident YourTable

group by ID,

Name,

Scale;

left Join(YourTable)

load *

resident Temp2;

drop table Temp2;

Re: Time since start

robin_heijt — Tue, 02 Oct 2018 08:21:26 GMT

Hi Dilip,

Thank you for your response.

It seems like it is heading in the right direction, however it is not exactly what I need.

As this now is the result:

Normally this person just has one line, however this code adds all the start dates for all the bands. I only need the latest date of the latest scale. How would I delete the former entries? In this case I only need the second to last one.

Re: Time since start

dplr-rn — Tue, 02 Oct 2018 13:26:37 GMT

Ok it seems i misunderstood your need. Whats the desired output on table you shared

e.g. my code should give you below

ID Name Scale Start Date MinStart

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

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

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

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

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

1000069 John Doe BWG 08 1/1/2018 1/1/2018

1000069 John Doe BWG 08 2/1/2018 1/1/2018

Re: Time since start

robin_heijt — Tue, 02 Oct 2018 13:40:59 GMT

Correct, that is what I get now.

However I only want the latest date (1/1/2018) to be shown. Then I want to use 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",

I order to calculate the time in the scale (Time in Band).

As seen in the picture I shared, I then only want one line which represents the person with the "Time in Scale" based on the latest date (1/1/2018)

Would you have an idea on how to achieve this?

Thank you in advance.

Re: Time since start

dplr-rn — Tue, 02 Oct 2018 13:44:12 GMT

Do you mean you want just below

ID Name Scale Start Date MinStart

1000069 John Doe BWG 08-V 10/1/2014 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

or your want

ID Name Scale Start Date MinStart

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

1000069 John Doe BWG 08 1/1/2018 1/1/2018

Re: Time since start

robin_heijt — Tue, 02 Oct 2018 13:57:27 GMT

ID Name Scale Start Date MinStart

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

Only this actually.

Re: Time since start

dplr-rn — Tue, 02 Oct 2018 14:58:17 GMT

Am i right in understanding that you only want the start date on the latest scale. that is convoluted. will need to think about it

Re: Time since start

dplr-rn — Tue, 02 Oct 2018 15:23:09 GMT

Below code should do it. A little complicated logic but it seems to work

basically i am creating a table (Temp3) which looks like this

ID Name Scale Start Date ScaleStart

1000069 John Doe BWG 08-V 10/1/2014 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

then using max Start date per ID, Name to assign the final value to right row that you want

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

Temp:

load *

Inline [

ID,Name,Scale,Start date

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

];

Temp3:

Load *,

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

resident Temp

;

ScaleMap2:

mapping load

AutoNumber(ID&Name), Max(ScaleStart) as MaxScaleStart

resident Temp3

group by ID, Name

;

Final:

Load *,

if(ApplyMap('ScaleMap2',AutoNumber(ID&Name),'')=[Start date],[Start date],'') as ScaleStartFinal

resident Temp

;

drop table Temp;

drop table Temp3;

Hope it helps.

Re: Time since start

robin_heijt — Tue, 02 Oct 2018 15:40:50 GMT

This is amazing, thank you so much for putting in this effort.

I have yet to try it, however I see you are using an inline table. Unfortunately this person "John Doe" is just 1 fake profile.

In reality I have more than 10.000 people in my file. How would I adapt the code to this situation?

Re: Time since start

dplr-rn — Tue, 02 Oct 2018 15:44:17 GMT

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

Re: Time since start

dplr-rn — Tue, 02 Oct 2018 16:34:21 GMT

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

Re: Time since start

robin_heijt — Wed, 03 Oct 2018 10:30:02 GMT

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.