How to expand rows based on time comparison

[Wen Wey Lee]

I have the following dataset:

 

 

 

Start Timestamp

End Timestamp

Event

 

 

15/06/2020 0:00

15/06/2020 0:04

A

 

 

15/06/2020 0:07

15/06/2020 0:10

B

 

 

 

 

 

 

 

 

 

 

 

I would like to expand the rows into the following:

 

 

 

Timestamp

Event

 

 

15/06/2020 0:00

A

 

 

15/06/2020 0:01

A

 

 

15/06/2020 0:02

A

 

 

15/06/2020 0:03

A

 

 

15/06/2020 0:04

A

 

 

15/06/2020 0:05

 

 

 

15/06/2020 0:06

 

 

 

15/06/2020 0:07

B

 

 

15/06/2020 0:08

B

 

 

15/06/2020 0:09

B

 

 

15/06/2020 0:10

 

B

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

It would be very much appreciated if anyone can help with this. Thanks.

[David Boot-Olazabal]

Hi wlee-O,

You first have to unpivot the data set. And then, you need to add the missing rows, based upon the start/enddate.

You can have a look at this post as a starting point for adding the missing rows:https://community.spotfire.com/wiki/ironpython-script-add-rows-zero

Kind regards,

David

[Wen Wey Lee]

Thanks for the reply. :)