Extend One Row to Multiple Rows by a Specific Rule
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.
SPL Official Website 👉 http://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc
SPL Learning Material 👉 http://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/ydhVnFH9
Youtube 👉 https://www.youtube.com/@esProc_SPL
Chinese version