Time series data with binary column analysis

SID101098SID101098 Member Posts: 3 Newbie
edited February 10 in Help
Hello there , i m trying to generate two timestamp columns (from,to) out of the table given below in such a way that whenever there is  '1' after '0' in the resultant column that particular timestamp should copied to the from column and whenever there is '0' after '1' that particular timestamp should be copied to the "to" column, this process should continue until all batches of '1' are found out. Can anyone help

Resultant	Date-time
0 31/12/2017 22:55
0 31/12/2017 22:27
1 31/12/2017 22:17
1 31/12/2017 21:55
0 31/12/2017 20:48
0 31/12/2017 11:03
1 31/12/2017 09:58
1 31/12/2017 09:52
1 31/12/2017 09:16
1 31/12/2017 08:11
1 31/12/2017 07:55
1 31/12/2017 06:54
1 31/12/2017 04:10
1 30/12/2017 12:15
1 30/12/2017 11:20
1 30/12/2017 10:50
1 30/12/2017 05:40
1 30/12/2017 04:49
1 30/12/2017 04:30
0 30/12/2017 04:00
0 30/12/2017 02:34
0 29/12/2017 23:02
1 29/12/2017 18:38
1 29/12/2017 08:04
1 29/12/2017 01:30
1 28/12/2017 23:59
1 28/12/2017 16:22
1 28/12/2017 14:43
1 28/12/2017 14:39
1 28/12/2017 14:39
1 28/12/2017 14:02
0 28/12/2017 13:46
0 28/12/2017 13:28
0 28/12/2017 12:51
0 28/12/2017 12:45
0 28/12/2017 12:24
1 28/12/2017 12:06

Best Answers

  • CKönigCKönig Administrator, Moderator, Employee, Member Posts: 1 RM Team Member
    Solution Accepted
    Hey and welcome to the community.

    If I understand your question correctly, you are trying to find the start and end date of the periods, during which the Resultant attribute has the value 1.

    The combination of Differentiate and Integrate (from Time Series) can find changes in an attribute and build a unique id per section without change. Afterwards a simple Aggregation finds last and first date.

    The same functionality is bundled in the Generate Session ID operator from the Operator Toolbox extension, using "none" as the gap unit.

    See the example process attached.

    Have a nice weekend!
    SID101098
  • SID101098SID101098 Member Posts: 3 Newbie
    edited February 11 Solution Accepted
    Hello there
    Thanks for the solution it worked perfectly! 
    Cheers
    MartinLiebigCKönig
Sign In or Register to comment.