Example1

In the Excel file book1.xlsx, part of the data is as follows:

Another file, book2.xlsx, has a table of the relationship between quantity interval and price, as shown in the figure below:

The task is to calculate the price value of column B in book1.xlsx. The calculation rule is to find the corresponding Price of Quantity in book2.xlsx. If the quantity is greater than Quantity1 and less than or equal to Quantity2, the price of the current row will be returned.

Write SPL script:

A1 Read the data of book1.xlsx

A2 Read the data of book2.xlsx

A3 Loop through each record of A1, use the segp function to query which segment number of the interval divided by Quantity1 of A2 the Quantity value is, and then take the price of the record with the corresponding number from A2 and assign it to Price. The option @r means to form a left-open and right-closed interval. For example, the number 50 is in the interval where the first row is located.

A4 Store the results in A1 to book1.xlsx

Example2

The Excel file book1.xlsx stores the data of car charging, and some of it is shown below:

Another Excel file book2.xlsx stores the corresponding price of different charging intervals, as shown in the following figure:

The task is to calculate the values of Price column in book1.xlsx, and the rule is to find the corresponding Price of the interval where the Starttime locates in book2.xlsx.

Write SPL script:

A1 Read the data of book1.xlsx

A2 Read the data of book2.xlsx, and @w option means to read them as a sequence of sequences

A3 Loop through each record in A1, use the Starthour sequence which starts from the 1^st row and the 2^nd column in A2 to form intervals. Then find the segment number of the interval where the Starttime locates, take the price of the corresponding number from the 3^rd row of A2 and assign it to Price.

A4 Store the result in A1 to book1.xlsx