Levels of Data in separate columns, bottom top approach

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

Comments

  • MarkSnodgrass
    edited January 2022

    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

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • 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?

  • Any ideas here?

  • @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.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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.

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

  • 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/

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Although its not my strength my I will look into that. Thanks for always helping @GrantSmith Appreciate that!

  • Credit to @MarkSnodgrass for offering that answer.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • Oops! Thank you @MarkSnodgrass.