Beast Mode: Dependency Identification

user091749

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 name	Project Status	Dependent Project	Dependent Project Status
A	Red	C	Green
B	Amber	A	Red
C	Green	B	Amber

 

Thanks!

GrantSmith

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

GrantSmith

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.

user091749

Hi,

 

Thanks. How would I do that?

 

 

GrantSmith

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