3.20 Merge same-order sequences in the original order and calculate intersection
Merge records of two same-structure tables in the original order by one or more fields and retain common records only. One task is to find how many sales records are stored in both online sales table in offline sales table.
| OrderID | Customer | SellerId | OrderDate | Amount |
|---|---|---|---|---|
| 10400 | EASTC | 1 | 2014/01/01 | 3063.0 |
| 10401 | HANAR | 1 | 2014/01/01 | 3868.6 |
| 10402 | ERNSH | 8 | 2014/01/02 | 2713.5 |
| 10403 | ERNSH | 4 | 2014/01/03 | 1005.9 |
| 10404 | MAGAA | 2 | 2014/01/03 | 1675.0 |
| … | … | … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =T(“Online.xlsx”) |
| 2 | =T(“Store.xlsx”) |
| 3 | =A1.sort(OrderID) |
| 4 | =A2.sort(OrderID) |
| 5 | =[A3,A4].merge@i(OrderID) |
| 6 | =A5.count() |
A1 Retrieve online sales table.
A2 Retrieve offline sales table.
A3 Sort online sales table by OrderID.
A4 Sort offline sales table by OrderID.
A5 merge function works with @i option to merge A3 and A4 by OrderID in the original order and return common records.
A6 Count the number of common order records.
Execution result:
| Value |
|---|
| 70 |
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
