Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
robin_heijt
Creator
Creator

Time since start

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

1 Solution

Accepted Solutions
dplr-rn
Partner - Master III
Partner - Master III

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
1000069John DoeBWG 07-V1/1/2014 
1000069John DoeBWG 08-V10/1/2014 
1000069John DoeBWG 08-V1/1/2015 
1000069John DoeBWG 08-V1/1/2016 
1000069John DoeBWG 08-V1/1/2017 
1000069John DoeBWG 08-V10/1/2017 
1000069John DoeBWG 081/1/20181/1/2018
1000069John DoeBWG 082/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.

View solution in original post

12 Replies
dplr-rn
Partner - Master III
Partner - Master III

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;

robin_heijt
Creator
Creator
Author

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:

Banding.PNG

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.

dplr-rn
Partner - Master III
Partner - Master III

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



robin_heijt
Creator
Creator
Author

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:

  1. // Time in Scale 
  2. Year(Today() - "Start date" + 1) - 1900 & ' Years, ' 
  3. & Num(Month(Today() - "Start hire date" + 1) - 1) & ' Months, ' 
  4. & 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.

dplr-rn
Partner - Master III
Partner - Master III

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

robin_heijt
Creator
Creator
Author

  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.

dplr-rn
Partner - Master III
Partner - Master III

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

dplr-rn
Partner - Master III
Partner - Master III

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
1000069John DoeBWG 07-V1/1/2014 
1000069John DoeBWG 08-V10/1/2014 
1000069John DoeBWG 08-V1/1/2015 
1000069John DoeBWG 08-V1/1/2016 
1000069John DoeBWG 08-V1/1/2017 
1000069John DoeBWG 08-V10/1/2017 
1000069John DoeBWG 081/1/20181/1/2018
1000069John DoeBWG 082/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.

robin_heijt
Creator
Creator
Author

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?