Beast mode - Join data from another line

user047019

Hi Domo community,

I would like to achieve a join from a single dataset.

Here is the column I have :

   ID, Parent_ID, Parents_status, Comment
   11     21                         2
   12     21                         3
   21                   Done         1 

I would like to do some beast mode to join data between lines and create two type of data:

Extract the parents status to apply it to the ID in the column Parent_status_New

And the child comment_sum which sum the number of comments attached

   ID, Parent_ID, Parents_status, Comment, Parent_status_New, Child_comment_sum
   11     21                         2           Done
   12     21                         3           Done
   21                   Done         1                            5

How should I create those beast mode? Do you have any idea how to do that?

Many thanks,

Julien

GrantSmith

HI @user047019

You won't be able to do it in a beast mode but you'll need to use a Magic ETL. You can have your original input dataset and then filter through two filter tiles. One filtering where Parent_ID is not null (children) and the other where Parent_ID is null (parents). Then you can use a join based on the children's Parent_ID with the parent's ID field. to get the Parent_status_New field. In addition you can take the output from your children filter into a group by and group by the Parent_ID field and count the ID field to get the Child_comment_sum field. Us a join to join this back to your parent data based on Parent_ID (which you group by) to the ID field from the parent filter.