Forum Discussion

fishboneox's avatar
fishboneox
Frequent Visitor
8 years ago

Copy previous row value including IF statement

Good morning, i know there are quite a few similar topics but unfortunately I'm not able yet to adopt them to my problem.

 

Localizing moving objects I'm using an inertial sensor on an Arduino board. Every time I notice movement I have created a flag value 1. Now I'm trying to get the Start and End Date of this movement and combine them to one "Drive". Calculating the start end value isn't the problem, but to create a row to identify them as one movement.

Currently my table is looking like this:

Since the value of this "Drive_ID" doesn't matter I was using the Index value for the first not blank value in the Start_movement column.

Now I try to Copy the value for Drive_ID from the row above as long Start-movement or End_movement isn't blank but somehow I'm not able to.

Any ideas of an easy solution? Is there any different way of how to get this information. The idea is to use a Gantt chart later to display

 

 

Need Help

4 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    8 years ago

    I’m guessing that the Start movement and End Movement Data is auto populated. Hi about this?

    1. Add a custom column concatenating start movement and end movement i to 1 column. 

    2. Remove any row in the new column that is Nul

    3. Delete column created in 1 

     

    this should leave just rows t have a time stamp. 

     

    Then fill the end movement column up. You may need to fill tblank cells with null first, depending on your data 

    then you should have all the start movements with the same end movement time.  

     

    Add a difference in time column. The longest (max) value for each end movement must be the correct row. 

     

    Hope it makes sense. I would need sample data to do this properly - this is just a best guess. 

    • fishboneox's avatar
      fishboneox
      Frequent Visitor
      8 years ago

      The Start and End Date is collected from a different column containing a key of date and time.

      To be able to use the data later for the Gantt chart I need a different start and end column linked by an ID (here Drive_ID).

       

       

       

      I'm not sure if I understand your answer. Here you can find some sample data:

       

       

      TimestampMovement Flag
      23.03.2018 09:41:250
      23.03.2018 09:41:260
      23.03.2018 09:41:270
      23.03.2018 09:41:280
      23.03.2018 09:41:290
      23.03.2018 09:41:300
      23.03.2018 09:41:311
      23.03.2018 09:41:321
      23.03.2018 09:41:331
      23.03.2018 09:41:341
      23.03.2018 09:41:351
      23.03.2018 09:41:361
      23.03.2018 09:41:371
      23.03.2018 09:41:381
      23.03.2018 09:41:391
      23.03.2018 09:41:400
      23.03.2018 09:41:410
      23.03.2018 09:41:420
      23.03.2018 09:41:431
      23.03.2018 09:41:441
      • MattAllington's avatar
        MattAllington
        Community Champion
        8 years ago

        I'm happy to have a go at it.  But I'm not clear what your starting position is.  You have provided an image with seemingly a before and after image, and also some sample data.  The "before" image and the sample data are different.  If you can provide some exact sample data, preferably with at least 3 complete events I will take a look (ideally in a table in Excel (Maybe linked via Dropbox or similar)