11.3 Recursively find references until reach the specified value
Based on the Organization table, find subordinate organizations of Beijing Branch Office and list their superior organizations. Separate multiple levels of organizations with the comma. Below is part of the source data:
| ID | ORG_NAME | PARENT_ID |
|---|---|---|
| 1 | Head Office | 0 |
| 2 | Beijing Branch Office | 1 |
| 3 | Shanghai Branch Office | 1 |
| 4 | Chengdu Branch Office | 1 |
| 5 | Beijing R&D Center | 2 |
| … | … | … |
When searching for superior organizations for an organization, stop recursion once the specified value (like Beijing Branch Office) is detected and retain the current organization. The unsearched records will be filtered away. To do this, SPL uses A.prior(F,r) function to recursively search for references until record r containing the specified value appears.
SPL script:
| A | |
|---|---|
| 1 | =T(“Organization.txt”) |
| 2 | >A1.switch(PARENT_ID,A1:ID) |
| 3 | =A1.select@1(ORG_NAME==“Beijing Branch Office”) |
| 4 | =A1.new(ID,ORG_NAME,~.prior(PARENT_ID,A3) :PARENT_NAME) |
| 5 | =A4.select(PARENT_NAME!=null) |
| 6 | =A5.run(PARENT_NAME=PARENT_NAME.(PARENT_ID.ORG_NAME).concat@c()) |
A1 Import Organization table.
A2 Objectify foreign key PARENT_ID and convert it to corresponding records of parent organizations – which is the process of foreign key objectification.
A3 Get the record of Beijing Branch Office.
A4 Create a new table consisting of ordinal number, organization name and sequence of records of all superior organizations.
A5 Get records whose superior organizations are null, which are those of/under Beijing Branch Office.
A6 Concatenate superior organization names into a comma-separated string in loop.
Execution result:
A4:
A5:
| ID | ORG_NAME | PARENT |
|---|---|---|
| 2 | Beijing Branch Office | [] |
| 5 | Beijing R&D Center | [[5,Beijing R&D Center,]] |
| 6 | Beijing Marketing Department | [[5,Beijing Marketing Department,]] |
| 7 | Beijing AI R&D Department | [[7,Beijing AI R&D Department,],[5,Beijing Marketing Department,]] |
| 8 | Beijing Internet R&D Department | [[8, Beijing Internet R&D Department,],[5,Beijing R&D Center,]] |
| 9 | … | … |
A6:
| ID | ORG_NAME | PARENT |
|---|---|---|
| 2 | Beijing Branch Office | |
| 5 | Beijing R&D Center | Beijing Branch Office |
| 6 | Beijing Marketing Department | Beijing Branch Office |
| 7 | Beijing AI R&D Department | Beijing R&D Center,Beijing Branch Office |
| 8 | Beijing Internet R&D Department | Beijing R&D Center,Beijing Branch Office |
| 9 | Beijing Internet Interface R&D department | Beijing Internet R&D Department,Beijing R&D Center,Beijing Branch Office |
| 10 | Beijing Market Research Team | Beijing Marketing Department,Beijing Branch Office |
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
