Beast Mode: Dependency Identification

user091749
user091749 Member
in Magic ETL

Hi.

 

I am trying to do a dependency status table for the project lifecycle.

 

I currently have columns for Project name, associated Project Status, and Dependent Project (which other project is required for this one to complete).

 

I am trying to write a statement to insert another column that identifies the status of the dependant project - so it will refer to column 2 to find the status of the project with the same name as the dependent project. I have given an example table below; Project C has status Green so it's good to go ahead, BUT depends on Project B to complete which is in Amber so it cannot go ahead yet. 

 

Project nameProject StatusDependent ProjectDependent Project Status
ARedCGreen
BAmberARed
CGreenBAmber

 

Thanks!

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @user091749 

    You'd need to use a MagicETL 2.0 data flow to join the data to itself.

     

    Take the input and put it to a Select Columns and select the Project Name and Status Columns and then join it back to your original data set based on original dataset dependent project = the select columns project name, resolve the conflicts by dropping the project name column in the right dataset and rename the project status column to be the Dependent Project Status.

     

    Here's screenshots outlining the process

    Screen Shot 2020-10-27 at 7.56.41 AM.pngScreen Shot 2020-10-27 at 7.57.04 AM.png

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach

    Hi @user091749 

     

    You're essentially wanting to do a join to a table to lookup the project status. Beast modes can't do this as they're processing on a record by record basis. The easiest and best option in this case is to utilize an ETL to filter the names and status and then join it back to the table based on the dependent project to include the dependent project status.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • user091749
    user091749 Member

    Hi,

     

    Thanks. How would I do that?

     

     

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Hi @user091749 

    You'd need to use a MagicETL 2.0 data flow to join the data to itself.

     

    Take the input and put it to a Select Columns and select the Project Name and Status Columns and then join it back to your original data set based on original dataset dependent project = the select columns project name, resolve the conflicts by dropping the project name column in the right dataset and rename the project status column to be the Dependent Project Status.

     

    Here's screenshots outlining the process

    Screen Shot 2020-10-27 at 7.56.41 AM.pngScreen Shot 2020-10-27 at 7.57.04 AM.png

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Categories