cancel
Showing results for 
Search instead for 
Did you mean: 

Joining hierarchical data

kiranm
New Contributor II

Hi,
I have an excel with the data in the below format
image

Rows at level 1 is a parent to the next subsequent rows at Level 2.
For ex: Development (row 2) is parent to Java, dotnet, php (rows 3,4,5)
Testing (Row 6)i s parent to Manual, Automation, Load (rows 7, 8, 9)

How can I merge/join each parent (Level 1) to its childs (Level 2)?
Desired output after joining:
Name, Parent
Java, Development
dotnet, Development
php, Development
Manual, Testing
Automation, Testing

Excel attached for referencehierarchy.xlsx (10.8 KB)

1 ACCEPTED SOLUTION

koryknick
Employee
Employee

Attached is a pipeline that should do this a little more generically, using a Script snap as Anubhav has suggested may be necessary. The script snap is necessary, mainly because SnapLogic works on data documents and does not hold any context between. The Script snap provides a way to process the entire stream and is able to maintain that context from one document to the next.

I hope this helps solve your problem!

Example_Flatten_Hierarchy_Data_2020_06_29.slp (7.6 KB)

View solution in original post

5 REPLIES 5

anubhav_nautiya
Contributor

Hi Kiran,
I have attached a pipeline to achieve this, hope this helps.

result: image

AN new pipeline 1_2020_06_29.slp (8.2 KB)

Regards
Anubhav

Hi Anubhav,
Thanks for taking time and creating the pipeline. This solutions works for the given data and you have used the below expression
($Description.contains(‘dev’) ? ‘Development’ : ($Description.contains(‘test’) ? ‘Testing’ : ‘Management’))

But the actual data in the excel that I want to process will have few 1000’s of records and the description will not be similar, so I can’t use this field. (description changed )

The logic should be based on the column ‘Level’
Basically, I need to iterate all the rows and assign the Name at Level 1 as parent to the subsequent rows until another Level 1 is found.
image

It would of great help if you can find a solution for this

well in that case you might have to write a script

koryknick
Employee
Employee

Attached is a pipeline that should do this a little more generically, using a Script snap as Anubhav has suggested may be necessary. The script snap is necessary, mainly because SnapLogic works on data documents and does not hold any context between. The Script snap provides a way to process the entire stream and is able to maintain that context from one document to the next.

I hope this helps solve your problem!

Example_Flatten_Hierarchy_Data_2020_06_29.slp (7.6 KB)