Problem description & analysis:
Data in the column below has three layers: the 1st layer is a string, the 2nd layer is a date, and the 3rd layer contains multiple time values:
1 NAME1
2 2024-06-03
3 04:06:12
4 04:09:23
5 08:09:23
6 12:09:23
7 17:02:23
8 2024-06-02
9 04:06:12
10 04:09:23
11 08:09:23
12 NAME2
13 2024-06-03
14 04:06:12
15 04:09:23
16 2024-06-02
17 12:09:23
18 17:02:23
We need to identify the three layers of data correctly and convert them to a standardized table:
1 NAME1 2024-06-03 04:06:12
2 NAME1 2024-06-03 04:09:23
3 NAME1 2024-06-03 08:09:23
4 NAME1 2024-06-03 12:09:23
5 NAME1 2024-06-03 17:02:23
6 NAME1 2024-06-02 04:06:12
7 NAME1 2024-06-02 04:09:23
8 NAME1 2024-06-02 08:09:23
9 NAME2 2024-06-03 04:06:12
10 NAME2 2024-06-03 04:09:23
11 NAME2 2024-06-02 12:09:23
12 NAME2 2024-06-02 17:02:23
Solution:
Use SPL XLL to type in the following formula:
SPL returns an integer for the date data. You need to format it into an easy-to-read form through Excel’s “format cells” option (or through SPL’s E() function). Use the same way to handle the time data.
As shown in the picture below:
E()function converts a value to the Excel date/time data; E@1 converts a multilayer sequence to a single-layer one. ~ represents the current member; if() function judges whether it is a string and whether it is a date from left to right and executes the expressions, and then executes the default expression. ifa() judges whether the variable is a sequence.
