"Perform a self-join on a single table and recursively get a specified field’s all levels of refe .."

mars RaqForum 25 No.
112 View • 1 Years ago

11.2 Recursively find all references in loop

Perform a self-join on a single table and recursively get a specified field’s all levels of references on all records.
Based on the Organization table, get the level of each department (head office is level 1, branch office is level 2, and so on). 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
…	…	…

Loop through each record to recursively locate all superior organizations for each department. SPL has A.prior(F) function to find references recursively. By default, it searches for all references.

SPL script:

	A
1	=T(“Organization.txt”)
2	>A1.switch(PARENT_ID,A1:ID)
3	=A1.new(ID,ORG_NAME,~.prior(PARENT_ID).len():LEVEL)

A1 Import Organization table.
A2 Objectify foreign key PARENT_ID and convert it to corresponding parent organization records to achieve the self-join.
A3 Create a new table consisting of ordinal number, department name and level. The level is calculated through A.prior() function, which recursively gets the number of levels a record references.

Execution result:

A1:

imagepng