Filtering data into different tables based on conditions

Hi all,

I have a table with rows of data relating to tasks. Using the following example below I have a few outcomes that I would like but I am unsure of the best way to do that, so open to suggestions


I want to create three tables effectively.
One where RO:Plan Review is present, and with 14 days, there is also an RT:Pre Tmt Chk present. if those conditions are met then it takes the entire row.
the 2nd table would be the same except it looks for the RT:Pre Tmt Check Replan, that is within 2 weeks of the RO:Plan Review.
The final table would be when there is an RO:Plan Review present, but no RT:Pre Tmt Chk, or RT:Pre Tmt Check Replan within 2 weeks.

sorry if that is super confusing. Hope you can help and thanks in advance

Hi @RGray ,
Could you please share a screenshot of how you want the output table to look based on the conditions that you have mentioned?


To add, it looks like you have more rows than the first three only. What are those like? Are the RO and two RT tows always on top of the table? It matters quite a lot for the logic to use here because you need to lead/lag with an offset higher than 1.

1 Like

Thanks for getting back to me,


This is what I am getting at the moment.


I’m honestly a bit to new to all of this to know what is the best way or the different ways to achieve it. Ultimately my end goal would be able to export to csv with the three tables each as their own sheet in a single workbook. Though I am just trying to work through it one step at a time.

I will add my code below as it may provide a bit more for you to work with, or if you have suggestions on how to improve the query to better filter the data in later nodes.

thanks again

Hi @RGray

I’m afraid we still need more clarification on your desired output because the data that you show leaves some open questions :wink:

I assume your db contains information of multiple patients meaning that you would have to evaluate for each patient individually if RO and the mentioned RT lines are present?

I think your table is supposed to be still sorted on Due_date?

If you could just share the columns Task_Name and Due_date in a workable format then I’m sure someone will be able to jump. It’s a bit long stretch to type all of this over in KNIME ourselves :wink:

Help us to help you.

@ArjenEX Do you mean to send the query as a text rather than a jpg image? Sorry for the hassle.

No the actual data table but without all the classified information.

Isn’t that what I shared with the tempsnip.jpg above?


Yes but I want to build something in KNIME that properly reflects your use case and it takes a long time to type it all over manually :wink:


So is this what you wanted?

Patient.Last_Name AS Patient_Last_Name,
Patient.First_Name AS Patient_First_Name,
QCLTASK.Description AS Task_Name,
CAST(Chklist.Due_DtTm As DATE) AS Due_Date,
Req_Staff.Last_Name AS Required,
Rsp_Staff.Last_Name AS Responsible,
CAST(Chklist.Act_DtTm AS DATE) AS Completed_Date,
FORMAT(Chklist.Act_DtTm, ‘HH:mm:ss’) AS Completed_Time,
Com_Staff.Last_Name AS Completedby,
QCLGroup.Description AS TaskSetGroup,
Chklist.Notes AS Comment,
Notes.notes AS Note_Message

JOIN Staff AS Req_Staff ON Req_Staff.Staff_ID = Chklist.Req_Staff_ID
JOIN Staff AS Rsp_Staff ON Rsp_Staff.Staff_ID = Chklist.Rsp_Staff_ID
JOIN Staff AS Com_Staff ON Com_Staff.Staff_ID = Chklist.Com_Staff_ID
JOIN Ident ON Chklist.Pat_ID1 = Ident.Pat_ID1
JOIN Patient ON Patient.Pat_ID1 = Ident.Pat_ID1
LEFT JOIN QCLGroup On QCLGroup.QGP_ID = Chklist.Chklist_ID
INNER JOIN Notes ON Notes.Note_ID = Chklist.Note_ID

WHERE Chklist.Complete = 1
AND (QCLTASK.Description LIKE ‘RT:%’ OR QCLTASK.Description Like ‘ROMP%’ OR QCLTASK.Description LIKE ‘RO:%’)
AND Chklist.Due_DtTm BETWEEN ‘2021-12-12 00:00:00.000’ AND ‘2023-12-14 00:00:00.000’

Typically you want to upload sample data with your question so forum members can work with the data in KNIME instead of creating data from scratch.
I assume @ArjenEX was referring to that and not the SQL Script.


This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.