Joining hierarchical data

[kiranm]

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

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)

[anubhav_nautiya]

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

result:

AN new pipeline 1_2020_06_29.slp (8.2 KB)

Regards
Anubhav

[kiranm]

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.

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

[anubhav_nautiya]

well in that case you might have to write a script

[koryknick · Accepted solution]

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)