2.14 Grouping: logical OR
Based on the following table recording customers whose order amounts rank in top 3 in each month of the past several years, find whether customer RATTC ever enters in top 3 in terms of monthly sales in each year.
| Year | Month | Top3 |
|---|---|---|
| 2019 | 1 | ANTON,AROUT,BERGS |
| 2019 | 2 | BLAUS,BLONP,BOLID |
| 2019 | 3 | BONAP,BOTTM,BSBEV |
| 2019 | 4 | CACTU,CENTC,CHOPS |
| 2019 | 5 | COMMI,CONSH,DRACD |
| 2019 | 6 | DUMON,EASTC,ERNSH |
| 2019 | 7 | FAMIA,FISSA,FOLIG |
| 2019 | 8 | FOLKO,FRANK,FRANR |
| 2019 | 9 | FRANS,FURIB,GALED |
| 2019 | 10 | GODOS,GOURL,GREAL |
| 2019 | 11 | GROSR,HANAR,HILAA |
| 2019 | 12 | HUNGC,HUNGO,ISLAT |
| … | … | … |
SPL script:
| A | |
|---|---|
| 1 | =T(“TopCustomers.xlsx”) |
| 2 | =A1.group(Year;~.(Top3.split@c().pos(“RATTC”)>0).cor():COR) |
A1 Load data table.
A2 Group table by Year and for each group use cor() function to find if customer RATTC appears in a certain month.
Execution result:
| Year | COR |
|---|---|
| 2019 | false |
| 2020 | false |
| 2021 | true |
| … | … |
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
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
