Derive Value From a foreign row on a column match

Hi, everyone
I am struggling to figure out a solution for something that’s relatively simple, I think. I am working with Jira data, attempting to tag a value based on a few data points. Those rules are as follows.

  1. For Milestones, any Milestone with a Target_End before 2022-12-31 is tagged Phase 1.
    any Milestone with a Target_End after 2022-12-31 (or a blank value) is tagged Phase 2.
  2. For Deliverables, any Deliverable with a Parent (Milestone) with a tag of Phase 2 is tagged Phase 2.

So my logic essentially needs to be:

Milestone tags =
$Target_end$ > “2022-12-31” => Phase2
MISSING $Target_end$ => Phase2
$Target_end$ <= “2022-12-31” => Phase1

All of that works fine, but where it’s wrong is that it’s tagging the deliverables of Milestones with the wrong phase if they are pre 2022-12-31 (this is an inheritable tag). I have highlighted these mis-tags in orange in the screenshot.

The next part is tricky (to me), I need to match IssueKey to ParentID and then pull the Phase from the Phase column into the Phase column of the child. I can’t do this with python or java, so I am limited to Rule Engine and Column Expressions.

I need WHERE ParentID = IssueKey, grab Phase from that foreign row and replace Phase in the original row.

Sorry if this is a dumb question or I missed it in the forum elsewhere.

I’ve included a screenshot of sample data and an excel file of that.


Untitled spreadsheet.xlsx (5.0 KB)

Hi @matthewthompson , Rule Engine and Column Expressions will definitely do.

I kind of have a general idea of what you want to do, but I think I need more precision though.

It’s always best to show what the input data is - in this case I am assuming that it’s the screenshot, and what the expected result would be, so that we can make sure that we correctly understood what’s being asked.

And I am trying to understand what this mean “I need WHERE ParentID = IssueKey, grab Phase from that foreign row and replace Phase in the original row.”

At first I thought you meant where ParentID = IssueKey of the same row, but I think that’s not what you meant cause you mentioned “foreign” row, which is not exactly clear either, but I can understand what you meant. But the question is, ParentID of which row and IssueKey of which row? Is it currentrow.ParentID = foreignrow.IssueKey? Or is it foreignrow.ParentID = currentrow.IssueKey?

And also:
o Can there exist currentrow.ParentID = currentrow.IssueKey?
o Can there exist multiple results of “WHERE ParentID = IssueKey, grab Phase from that foreign row and replace Phase in the original row”?

1 Like

Hi, sorry for being unclear. I did include an Excel version of the screenshot attached to the post.

It is essentially:

Inherit phase status of foreignrow
where currentrow.ParentID = foreignrow.IssueID

as I am searching for an inheritable phase status of child IDs. I think I am having trouble making the jump of grabbing an attribute of a foreign row on a non 1/1 match (ie, return the value of column x when currentrow.parentid matches foreignrow,issueid)

Hi @matthewthompson , no problem and thank you for clarifying :slight_smile:

This can be done via a Left Join and Rule Engine.

Something like this will do:
image

Input data (Your Excel file):
image

Results:
image

Here’s the workflow:

1 Like

@bruno29a Forgive me for asking. What does the dot mean in currentrow.ParentID?

Hello, I think we were utilizing the dot to delineate that the value should come from the cell in the identifier before the dot (SQL reference, I think). @bruno29a I really appreciate your patience and problem solving skills. This is exactly what I was looking for and I hadn’t even considered an outer join using the same table twice! This will really help me in my KNIME development

2 Likes

Hi @badger101 , as @matthewthompson explained, it was meant to indicate which field for which row, kinda the same format of what db statements use such as table1.column4 which indicates column4 from table 1.

@matthewthompson , no problem, my pleasure.

2 Likes