dear KNIME user ,
i have two table
Table 1
Count / Comment/ Start date Time / End Date Time
1 A /2023-11-09 18:15:33/2023-11-09 18:25:48
1 A /2023-11-09 19:40:32/2023-11-09 19:50:38
Table 2
Count / Comment/ Start date Time / End Date Time
11 DA /2023-11-09 18:15:33/2023-11-09 18:18:48
12 CA /2023-11-09 18:18:48/2023-11-09 18:20:48
14 FA /2023-11-09 18:20:48/2023-11-09 18:25:48
11 DA /2023-11-09 19:40:32/2023-11-09 19:42:38
13 CA /2023-11-09 19:42:38/2023-11-09 19:46:38
18 FA /2023-11-09 19:46:38/2023-11-09 19:50:38
OutPut table
Count / Comment/ Start date Time / End Date Time / Remarks
1 A /2023-11-09 18:15:33/2023-11-09 18:25:48 Event
11 DA /2023-11-09 18:15:33/2023-11-09 18:18:48 Reason
12 CA /2023-11-09 18:18:48/2023-11-09 18:20:48 Reason
14 FA /2023-11-09 18:20:48/2023-11-09 18:25:48 Reason
1 A /2023-11-09 19:40:32/2023-11-09 19:50:38 Event
11 DA /2023-11-09 19:40:32/2023-11-09 19:42:38 Reason
13 CA /2023-11-09 19:42:38/2023-11-09 19:46:38 Reason
18 FA /2023-11-09 19:46:38/2023-11-09 19:50:38 Reason
I would like make a output table as shown above using loop node in KNIME. Reason row start time and End time should be within Event row Start time and End time. if this condition not met, then the Remarks column shows Error. Any help greatly appreciated.
I think you need to add a little more context and explanation.
You have the words “Event” and “Reason” appearing as remarks in your output table, and you mention “Reason row” start/end should be within “Event row” start/end.
But… what is a “reason row” and what is an “event row” in the context of Table 1 and Table 2? I’m guessing Table 2 is “reason” and Table 1 is “event”. Is that right? I don’t think you said that anywhere.
Your output table appears to be listing all the rows from Table 1 and Table 2 in order of start dates.
But you say that “if the condition [Reason row start/end within Event start/end] is not met, then Remarks column shows Error.”. Which row or rows should show “Error” the Event or Reason rows? Perhaps you can give an example of such an error, and what the output would look like.
Also, thank you for supplying some textual sample data, but can you either upload csv/xlsx of your sample data, or be consistent in the use of delimiters please, as it is a little painful for people to have to transform your data before they can start to look at the problem.
Finally… is there a particular reason that you want to use a loop node? Have you an idea in mind of how a loop will be of help here? It may be that a loop is needed, but I don’t know that yet. I use them only for specific tasks or when I have run out of better ideas
At the moment, subject to further information, my thoughts don’t include a loop and are this:
Concatenate → Sorter → Rule Engine → Rule Engine → Missing Values → Rule Engine
@takbb Thank for the reply. I apologize for the missing information.
As you said Table 1 is for Event and Table 2 for Reason
Once again, I’m deeply sorry not to upload the csv/xlsx due to several constrain on uploading excel files
I am rephrasing the words little and shown again below
Table 1 (Event)
RowID/ Count / Comment/ Start date Time / End Date Time
300/1 /A /2023-11-09 18:15:33/2023-11-09 18:25:48
301/1/ A /2023-11-09 19:40:32/2023-11-09 19:50:38
302/1/ A /2023-11-09 20:10:32/2023-11-09 20:20:38
Table 2 (Reason)
RowID/Count / Comment/ Start date Time / End Date Time
400/11/ DA /2023-11-09 18:15:33/2023-11-09 18:18:48
401/12/ CA /2023-11-09 18:18:48/2023-11-09 18:20:48
402/14/ FA /2023-11-09 18:20:48/2023-11-09 18:25:48
403/11/ DA /2023-11-09 19:40:32/2023-11-09 19:42:38
404/13/ CA /2023-11-09 19:42:38/2023-11-09 19:46:38
405/18/ FA /2023-11-09 19:46:38/2023-11-09 19:50:38
406/13/ MA /2023-11-09 21:42:38/2023-11-09 21:46:38
407/18/ CA /2023-11-09 21:46:38/2023-11-09 21:50:38
OutPut table
RowID/Count / Comment/ Start date Time / End Date Time / Remarks
300/1 A /2023-11-09 18:15:33/2023-11-09 18:25:48 /Event
400/11 DA /2023-11-09 18:15:33/2023-11-09 18:18:48 /Reason
401/12 CA /2023-11-09 18:18:48/2023-11-09 18:20:48/ Reason
402/14 FA /2023-11-09 18:20:48/2023-11-09 18:25:48/ Reason
301/1 A /2023-11-09 19:40:32/2023-11-09 19:50:38/ Event
403/11 DA /2023-11-09 19:40:32/2023-11-09 19:42:38 /Reason
404/13 CA /2023-11-09 19:42:38/2023-11-09 19:46:38/ Reason
405/18 FA /2023-11-09 19:46:38/2023-11-09 19:50:38 /Reason
302/1/ A /2023-11-09 20:10:32/2023-11-09 20:20:38/ Event
406/13/ MA /2023-11-09 21:42:38/2023-11-09 21:46:38 /Error
407/18/ CA /2023-11-09 21:46:38/2023-11-09 21:50:38/ Error
As you can see in the output table rows 406 and 407 are identified as error because these rows start time and end time range do not fall within the Event 302 row start/End time range.
In addition, specifically here I want to use a loop node option for row by row comparison of two table and making the decision. Thanks
if i got you correct and this is what your example tells me, you simple search for Reasons in table2 which are after the last event.
I suggest to identify the last/maximum event in table 1 using group by node and join this result to table 2 using cross joiner node or if you have key-information use joiner node. Then you can simple check each row if it is outside.
Hi @morpheus ,
thanks for the suggestion. using the cross-joiner node perfectly works in this scenario. But now I’m trying with Loop node concept with If Switch and Rule engine node. I would apricate any suggestion on this workflow.
Hi @Vaamen, thanks for the additional information and the new sample data. If you aren’t needing loops, then this workflow uses the sequence of nodes I described in my earlier post
and produces the requested output, along with the start and end of the event with which it was compared (you can filter those columns out if not needed)
I can’t see how this would easily be achieved with a loop, but if there is still something you need to do in addition to producing the output table, then this can be looked at.
Hi @takbb ,
Thank you very much for taking time to construct the workflow and of course it seamlessly works.
The purpose why I would like to use loop here is that first select the first row from Table 1 (Event) and compare the start/end time range of Table 2 (Reason), if the condition met then it will go for several other If-else condition. Then the second loop cycle start from 2nd row of Table 1 and so on.
Thanks
