RaqForum 28 No.
1 Reply  •  442 View  • 
     ...

Extend One Row to Multiple Rows by a Specific Rule

 
excel(115)

The Excel worksheet below consists of two columns of start time and end time:

A

B

1

FeedOff

FeedOn

2

2020-09-19 06:00:00

2020-09-22 06:00:00

3

2020-09-28 06:00:00

2020-09-28 18:00:00

4

2020-10-01 19:00:00

2020-10-06 19:00:00

Task: Extend each row of start time & end time to multiple rows according to the number of consecutive dates it includes and calculate the number of hours each date covers. The following is part of the expected result (where the first row is extended into 4 rows):

A

B

1

2020-09-19

18

2

2020-09-20

24

3

2020-09-21

24

4

2020-09-22

6

5

2020-09-28

12

6

2020-10-01

5

7

2020-10-02

24

8

2020-10-03

24

9

2020-10-04

24

10

2020-10-05

24

11

2020-10-06

19

Directions:

1. Start esProc (Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time).

2. Write and execute script in esProc.

A

B

1

=file("data.xlsx").xlsimport@t()

2

for A1

=periods@s(A3.FeedOff,A3.FeedOn,3600)

3

=B3.groups(date(~);count(1))

4

=file("result.xlsx").xlsexport@a(B3)

In this esProc script, periods()function generates a continuous time sequence at a specific interval apart. @s option enables generating the time sequence in seconds; parameter 3600 means on hour apart. groups() function performs grouping and summarization.


excel(115)
 
 •  442 View  •