Levels of Data in separate columns, bottom top approach

User2021 · 2022-01-21T21:53:16+00:00

There was an error rendering this rich post.

User2021

Hello,

I am working on a request to provide all level of parent values for each individual child value. It is kind of bottom top approach. I have to start from a child ID column. For each child I need multiple cols of Parent ID. Something like below:

The current data looks like

I am trying to solve this in ETL. Any suggestions how to plan ETL? TIA

Find more posts tagged with

Comments

MarkSnodgrass

I would use the Pivot Tile in Magic ETL to do this. This is what it is designed for. You can read more about how to do it in this KB article:

https://domohelp.domo.com/hc/en-us/articles/360044951294-New-Magic-ETL-Tiles-Pivot

User2021

Thanks @MarkSnodgrass . I should have been clear in my post. It has to be related too. child>parent>grand parent> great grand parent ......

It is bottom top so I need all parents in separate columns starting from a child ID. Any idea how to do it in ETL? I can join child and parent but I am confuse how many time I would join and what to do once there is no Parent for that particular child?

User2021

Any ideas here?

GrantSmith

@User2021

How are you able to relate the child to a grandparent or great grand parent? You data appears to show that the parent (numbers) never being in the child column (letters).

What you're asking to do, If I'm understanding you correctly, is to have Domo recursively go down the "family tree". Domo can't exactly do this for you, you'd have to specify the number of levels you're wanting to go down.

Assuming your parents can be found as a child you'd essentially have multiple joins to each level down the tree you wanted to go. You can keep left joining the dataset together based on parent id = child id and renaming the columns to parent 1, parent 2, parent 3 as you go down your list to however far out you'd like. You'll need to have another tile after your dataset (I'd use a select columns and rename your Parent ID field to Parent 1) since you can't join the same tile to itself.

User2021

Yeah makes sense. So far whatever I see , there is no specific number of level it should go down. I have been asked to create a dataset starting with child and then parent>grandparent and so on till whenever it ends.

Thanks @GrantSmith I may come back if I get stuck here.

User2021

Just thinking, is it doable in Mysql? Is there anyone who can help me in Mysql?

MarkSnodgrass

Domo uses MySQL 5.6 so keep that in mind as far as leveraging recursive features in MySQL. This article might be of help to you.

https://explainextended.com/2009/07/20/hierarchical-data-in-mysql-parents-and-children-in-one-query/

User2021

Although its not my strength my I will look into that. Thanks for always helping @GrantSmith Appreciate that!

GrantSmith

Credit to @MarkSnodgrass for offering that answer.

User2021

Oops! Thank you @MarkSnodgrass.