How to calculate elapse time between two dates in the same field

[pentaxadmin]

Dear Community:

I have a challenge to calculate elapse time between different time stamps of steps in activity.

ID, Activity ID, Step ID, CreateDate,

1, 1, 1-1, 1/1/16 05:00:10,

2, 1, 1-3, 1/1/2016 05:05:00,

3, 1, 1-5, 1/1/2016 06:10:00

I am trying to create ElapseTime Fields that will calculate the time difference between the timestamps in the same activity

My result field should look like the following

ID, ID, Activity ID, Step ID, CreateDate, ElapseTime

1, 1, 1-1, 1/1/16 05:00:10, 0

2, 1, 1-3, 1/1/2016 05:05:00, 00:04:50

3, 1, 1-5, 1/1/2016 06:10:00, 01:05:00

Thanks for your help once again.

[vinieme12]

You should be using ActivityID not ID I think Id here is just rownumber

ActivityID and StepId are related to each other not ID

USING YOUR SCRIPT __CHANGES IN BOLD

[InstanceSteps]:

LOAD * INLINE [

ID, ActivityID, StepId, CreateDate,

1, 1, 1-1, 2016-01-01 00:05:00,

2, 2, 2-1, 2016-05-11 00:05:00,

3, 1, 1-3, 2016-01-01 00:06:00,

4, 1, 1-5, 2016-01-02 00:06:00,

5, 2, 2-3, 2016-05-11 00:10:00

];

NOCONCATENATE

TimeLapsSteps:

LOAD

*

,

// StepId,

IF(ActivityID = Previous(ActivityID),

time(rangesum(Interval(Timestamp#(CreateDate,'YYYY-MM-DD hh:mm:ss')- Timestamp#(previous(CreateDate),'YYYY-MM-DD hh:mm:ss'), 'hh:mm:ss'),peek(ElapseTime))),null() )    AS ElapseTime

resident [InstanceSteps] Order by ActivityID,StepId;

DROP TABLE [InstanceSteps];

[pentaxadmin]

It is not assigning correct previous date. Previous date should be only associated within the same activityID

So, for ActivityID = 1 I have 3 steps and for ActivityID = 2 I have 2 steps. I need to calculate lapse time between the steps for each activity

[vinieme12]

change to below

IF(ActivityID = Previous(ActivityID),

time(Interval(Timestamp#(CreateDate,'YYYY-MM-DD hh:mm:ss')- Timestamp#(previous(CreateDate),'YYYY-MM-DD hh:mm:ss'), 'hh:mm:ss')),null() )    AS ElapseTime

[pentaxadmin]

Thanks Pujari,

It looks like the first code was working with slight delta when I did compare in excel, maybe rounding issue, but the change you sent me works great so far. will let you know once I plug it into my model if I am getting the same results

ID	ActivityID	StepId	CreateDate	ElapseTime	check	delta
2	2	2-1	2016-05-11 00:05:00	-
5	2	2-3	2016-05-11 00:10:00	0.0034722222263	0.003472222	-
1	1	1-1	2016-01-01 00:05:00	-
3	1	1-3	2016-01-01 00:06:00	0.0006944444467	0.000694444	-
4	1	1-5	2016-01-01 01:06:00	0.0416666666642	0.0416666667	-
-

[vinieme12]

Glad to help

Cheers

V

[pentaxadmin]

Thanks. I got it working, I am having some issues with some calculations, but I believe that's due to the date formats.

Thank you again for your help. !!!

[vinieme12 · Accepted solution]

Can you mark a post as correct and close this thread please.

[AshwinDaniel]

Dear Connections,

What function do I need to use for sum up all Elaspse Time? as a KPI.

Regards,

Ashwin Daniel