Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our May 2021 end-to-end product release from Data Integration to Data Analytics is out! READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
bujnakbranislav
Partner
Partner

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

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.

1 Solution

Accepted Solutions
vinieme12
Champion II
Champion II

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

View solution in original post

16 Replies
galax_allu
Specialist
Specialist

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

bujnakbranislav
Partner
Partner
Author

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

bujnakbranislav
Partner
Partner
Author

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
Champion II
Champion II

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
Champion II
Champion II

Check your Order By statement

it should be by ID,StepID

You don't need CreatedDate in Orderby clause

vinieme12
Champion II
Champion II

please see my updated post below,

Attaching APP for reference

flores_mig
Partner
Partner

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;

interval.JPG

Regards!

Mike

bujnakbranislav
Partner
Partner
Author

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:

 

IDActivityIDStepIdCreateDateElapseTime
111-12016-01-01 00:05:00-
311-32016-01-01 00:06:00-
411-52016-01-02 00:06:00-
222-12016-05-11 00:05:00-
522-32016-05-11 00:10:00-
bujnakbranislav
Partner
Partner
Author

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:

  

IDActivityIDStepIdCreateDatePrevious_DateElapseTime
111-12016-01-01 00:05:00--
311-32016-01-01 00:06:002016-05-11 00:05:00-131.00
411-52016-01-02 00:06:002016-01-01 00:06:001.00
222-12016-05-11 00:05:002016-01-01 00:05:00131.00
522-32016-05-11 00:10:002016-01-02 00:06:00130.00