App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:
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
Partner

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:

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:

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

resident Temp

;

ScaleMap2:

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

resident Temp3

group by ID, Name

;

Final:

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

resident Temp

;

drop table Temp;

drop table Temp3;

Hope it helps.

12 Replies
Partner

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

Temp2:

ID,

Name,

Scale,

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

resident YourTable

group by ID,

Name,

Scale;

left Join(YourTable)

resident Temp2;

drop table Temp2;

Creator
Author

Hi Dilip,

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.

Partner

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

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:

```
// 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?

Partner

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

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

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.

Partner

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

Partner

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:

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:

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

resident Temp

;

ScaleMap2:

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

resident Temp3

group by ID, Name

;

Final:

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

resident Temp

;

drop table Temp;

drop table Temp3;

Hope it helps.

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?

Tags
Community Browser