11.4 Find leaf-level records
Find regions and counties under Hebei province in China administrative division table. Below is part of the table:
| ID | NAME | PARENT_ID |
|---|---|---|
| 1 | China | 0 |
| 11 | Beijing | 1 |
| 12 | Tianjin | 1 |
| 13 | Hebei | 1 |
| … | … | … |
| 1301 | Shijiazhuang | 13 |
| 1302 | Tangshan | 13 |
| … | … | … |
This task involves searching for all subordinate leaf records under a specified parent record. SPL offers P.nodes@d(F,r) to find all leaf records recursively.
SPL script:
| A | |
|---|---|
| 1 | =T(“ChinaRegion.csv”) |
| 2 | >A1.switch(PARENT_ID,A1:ID) |
| 3 | =A1.select@1(NAME==“Hebei”) |
| 4 | =A1.nodes@d(PARENT_ID,A3) |
| 5 | =A4.new(ID,NAME,PARENT_ID.NAME:PARENT_NAME) |
A1 Import ChinaRegion table.
A2 Objectify the foreign key PARENT_ID and convert it to corresponding parent region records.
A3 Select the record of Hebei province.
A4 Use P.nodes() function to perform a recursive searching. The function works with @d option to find all leaf records recursively.
A5 Generate a new table consisting of ID, region name and parent region name.
Execution result:
A4:
| ID | NAME | PARENT_ID |
|---|---|---|
| 130102 | Changan District | [1301,Shijiazhuang,] |
| 130104 | Qiaoxi District | [1301,Shijiazhuang,] |
| 130105 | Xinhua District | [1301,Shijiazhuang,] |
| 130107 | Jingxing mining area | [1301,Shijiazhuang,] |
| 130108 | Yuhua District | [1301,Shijiazhuang,] |
| 130109 | Gaocheng District | [1301,Shijiazhuang,] |
| 130110 | Luquan District | [1301,Shijiazhuang,] |
| 130111 | Luancheng District | [1301,Shijiazhuang,] |
| 130121 | Jingxing County | [1301,Shijiazhuang,] |
| 130123 | Zhengding County | [1301,Shijiazhuang,] |
| 130125 | Xingtang County | [1301,Shijiazhuang,] |
| 130621 | Lingshou County | [1301,Shijiazhuang,] |
| … | … | … |
A5:
| ID | NAME | PARENT_NAME |
|---|---|---|
| 130102 | Changan District | Shijiazhuang |
| 130104 | Qiaoxi District | Shijiazhuang |
| 130105 | Xinhua District | Shijiazhuang |
| 130107 | Jingxing mining area | Shijiazhuang |
| 130108 | Yuhua District | Shijiazhuang |
| 130109 | Gaocheng District | Shijiazhuang |
| 130110 | Luquan District | Shijiazhuang |
| 130111 | Luancheng District | Shijiazhuang |
| 130121 | Jingxing County | Shijiazhuang |
| 130123 | Zhengding County | Shijiazhuang |
| 130125 | Xingtang County | Shijiazhuang |
| 130621 | Lingshou County | Shijiazhuang |
| … | … | … |
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
