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.
- 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.
- 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 
This can be done via a Left Join and Rule Engine.
Something like this will do:
Input data (Your Excel file):
Results:
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
