Beast Mode - Parent of Parent type join

mburstein
mburstein Member
in Magic ETL

I have a data set of project items at multiple levels of a project hierarchy: project > task > sub task. I'm trying to build a sort of rollup card that shows the items based on parent link relationships.   i.e.:

  1. Project Parent = Self
  2. Task Parent = Project
  3. Sub Task Parent = Task

I'd like to be able to have a column that shows the top level project ID for a sub task but I don't actually have the project associated to a sub task in my data set.  I only have the sub tasks parent task.  Is it possible to use beast mode or another solution to get something like:

 

CASE WHEN `type`='subtask' THEN `subtask_parent.task_parent` 

Where `subtask_parent.task_parent` is the subtask's parent's parent (the project)?

 

Just looking for options. 

 

 

Tagged:

Comments

  • Valiant
    Valiant Coach

    This almost sounds like a join. 

     

    Simple syntax for something like that would be:

    SELECT 
    FROM Dataset AS p 
    LEFT JOIN 
    (SELECT *
    FROM Dataset
    WHERE type = task) AS t 
    ON t.task_parent = p.project
    LEFT JOIN 
    (SELECT *
    FROM Dataset
    WHERE type = subtask) AS s 
    ON s.subtask_parent = t.task_parent
    WHERE p.`type` = 'parent'

    If something like that is possible with your data, that should get you to what you're looking for.

     

    Sincerely,

    Valiant

Categories