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.

[Anonymous]

Hi

not tested , try this

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

[pentaxadmin]

It works, but once I have multiple records it seems not to work properly

I need to make sure that timelaps are calculated for each activity between the steps

[pentaxadmin]

I am getting -

[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

];

TimeLapsSteps:

LOAD

StepId,

IF(StepId= previous(StepId),

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

resident [InstanceSteps] Order by CreateDate,StepId;

[vinieme12]

Your load script will be as follows

TABLE1:

LOAD

ID,

StepID,

DATE(Dateval,'DD/MM/YYYY hh:mm:ss') as CreateDate

FROM

(ooxml, embedded labels, table is Sheet1);

NOCONCATENATE

TABLE2:

LOAD

*,

if(ID = Previous(ID), time(rangesum(Interval(Timestamp#(CreateDate,'DD/MM/YYYY hh:mm:ss')- Timestamp#(Previous(CreateDate),'DD/MM/YYYY hh:mm:ss'), 'hh:mm:ss'),peek(ElapseTime))),null() )    AS ElapseTime

RESIDENT TABLE1

ORDER BY ID,StepID;

Drop Table TABLE1;

[vinieme12]

Check your Order By statement

it should be by ID,StepID

You don't need CreatedDate in Orderby clause

[vinieme12]

please see my updated post below,

Attaching APP for reference

[flores_mig]

Hi Branislav.

Hope this helps....

Data:

LOAD * INLINE [

    ID,  Activity ID,  Step ID,  CreateDate

    1,1,1-1,01/01/2016 05:00:10 AM

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

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

];

Calc_prev_date:

LOAD

  *,

  Previous(CreateDate) as Previous_Date,

  Interval(CreateDate - Previous(CreateDate)) as elapsed_time

Resident Data;

Drop Table Data;

Regards!

Mike

[pentaxadmin]

It's still doesn't work. I can not see your attached example, but here is the script and result

[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(ID = Previous(ID),

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 ID,StepId;

DROP TABLE [InstanceSteps];

Result:

ID	ActivityID	StepId	CreateDate	ElapseTime
1	1	1-1	2016-01-01 00:05:00	-
3	1	1-3	2016-01-01 00:06:00	-
4	1	1-5	2016-01-02 00:06:00	-
2	2	2-1	2016-05-11 00:05:00	-
5	2	2-3	2016-05-11 00:10:00	-

[pentaxadmin]

Does not work: See my script below and results:

[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

];

Calc_prev_date:

LOAD

  StepId,

  Previous(CreateDate) as Previous_Date,

  Interval(CreateDate - Previous(CreateDate)) AS ElapseTime

Resident [InstanceSteps];

Results:

ID	ActivityID	StepId	CreateDate	Previous_Date	ElapseTime
1	1	1-1	2016-01-01 00:05:00	-	-
3	1	1-3	2016-01-01 00:06:00	2016-05-11 00:05:00	-131.00
4	1	1-5	2016-01-02 00:06:00	2016-01-01 00:06:00	1.00
2	2	2-1	2016-05-11 00:05:00	2016-01-01 00:05:00	131.00
5	2	2-3	2016-05-11 00:10:00	2016-01-02 00:06:00	130.00