Solved: Re: Time since start - Qlik Community

robin_heijt · ‎2018-10-01

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

dplr-rn · ‎2018-10-02

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.

View solution in original post

dplr-rn · ‎2018-10-01

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 · ‎2018-10-02

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.

dplr-rn · ‎2018-10-02

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 · ‎2018-10-02

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.

dplr-rn · ‎2018-10-02

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 · ‎2018-10-02

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 · ‎2018-10-02

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 · ‎2018-10-02

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.

robin_heijt · ‎2018-10-02

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?