Levels of Data in separate columns, bottom top approach

Options
User2021
User2021 Member
in Magic ETL

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:

image.png

The current data looks like

image.png


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

Comments

  • MarkSnodgrass
    MarkSnodgrass Coach
    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.
  • User2021
    User2021 Member

    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
    User2021 Member

    Any ideas here?

  • GrantSmith
    GrantSmith Coach

    @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!**
  • User2021
    User2021 Member

    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
    User2021 Member

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

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.
  • User2021
    User2021 Member

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

  • GrantSmith
    GrantSmith Coach

    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!**
  • User2021
    User2021 Member

    Oops! Thank you @MarkSnodgrass.

Categories