# Getting time periods from consecutive timestamps and activities per user

``````uid	activity	timestamp
123	Activity1	1370520169521
123	Activity1	1370520279532
123	Activity2	1370520319543
123	Activity2	1370520419554
123	Activity2	1370520569565
123	Activity1	1370520679576
123	Activity1	1370520779587
123	Activity1	1370520879598
123	Activity1	1370520979612
``````

For each user, I want to create the beginning and end of a time period when an activity starts and when it ends.

``````uid	activity	start				end
123	Activity1	1370520169521		1370520279532
123	Activity2	1370520319543		1370520569565
123	Activity1	1370520679576		1370520979612
``````

How can I get these periods?

Hi @svki0001 , there isnâ€™t an out of the box solution for finding the first and last row in a grouping. There are techniques that can be employed using â€śmoving aggregationâ€ť or â€śrankingâ€ť nodes, to provide an ordinal position which can then be used to find the start and end of a grouping but your groupings do not have a â€śuniqueâ€ť key (combination of uid and activity) as this can be repeated as per your example data. Such solutions would therefore probably find the very first, and very last item in each group which isnâ€™t what you want.

For such a purpose, it is possible to use the java snippet to find the groupings. If you know some java, or are interested in learning more about that then could take a look at my post â€śjava snippets have long memoriesâ€ť post to get an idea of how such a snippet can work, but that isnâ€™t necessary if you donâ€™t wish to as I encapsulated the functionality required to assist with such a task into a component on the hub:

It possibly isnâ€™t immediately apparent how finding a â€ścumulative sumâ€ť is going to help, but hopefully that will become clearer in a moment.
What we can do is first of all create a â€śgrouping keyâ€ť to identify rows which belong to the same group. This will be uid and activity. For this, you could use any node capable of joining two columns into a new value, such as `String Manipulation`, `Column Combiner`, `Column Expression`s, `Rule Engine` and probably many others. I will use `Column Combiner` because its purpose is then reasonably self-explanatory. It will concatenate the uid and activity, delimited by a â€ś:â€ť.

After this, a Math Formula can be used to place an integer 1 into a new column (called key-sum).

This column will then cumulatively summed for all occurrences of the same â€śkeyâ€ť, until the value of â€śkeyâ€ť changes. This is where the Cumulative Sum comes in, and it will generate a column I have called â€śgroup-posâ€ťâ€¦

By cumulatively summing the key-sum column, Group Pos now identifies the ordinal row position for each row within each current group.

A `Lag Column` node then places on the row, the Group Pos for the next row.

This is important because it will define the start and end row for each group:

• The start of a group is identified by rows with a group-pos column value of 1
• The end of a group is identified by rows with a group-pos(-1) column value of 1 (i.e the next row is the start of a new group)

A pair of Rule Engines can then use the above rules to place into a new column the value of the timestamp but only for rows which are start or end of a group.

Using a `Missing Value` node to populate each missing end-timestamp with the next available end-timestamp helps us define rows that now show both a start-timestamp and an end-timestamp for the group:

Finally a row-filter can remove any row that is missing a start-timestamp, and a column filter to remove the superfluous columns, gives the table that you are looking for:

Bringing it all together:

(The initial â€śnodeâ€ť is actually just a component that allowed me to copy/paste your example easily)

I hope that helps. You can of course look inside the `Cumulative Sum for Grouping` component if you want to see what the java snippet looks like to achieve this.

4 Likes

See this wf consecutive_timestamps.knwf (30.1 KB)

just another approach
gr. Hans

4 Likes

Nice solution @HansS . Very neat!

1 Like

thank you Brian @takbb
In this case, I even surprised myself

1 Like

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.