3.19 Merge-union same-order sequences in the original order
Merge records of two same-structure tables in the original order by one or more fields and remove duplicate records.
A business’s sales records are stored in online store table and offline store table separately according to different sales channel. Some sales records are stored in both tables when there are both online and offline promotions. Now we are trying to find the actual total sales amount. The two tables have same structure, as shown below:
| 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@u(OrderID) |
| 6 | =A5.sum(Amount) |
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 @u option to merge A3 and A4 by OrderID in the original order and delete duplicate records.
A6 Calculate total sales amount.
Execution result:
| Value |
|---|
| 678756.41 |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL
