Stop Workflow If Unmatched Rows in Joiner

[Henan]

Hi everyone,

I’m joining tables A and B using the “MemberID” column in each table. In case that there are unmatched rows I want the workflow to stop. Unmatched rows indicate that a new member (and accordingly a new Member ID) has been added to table A. In that case the member and their ID should also be added to table B.

To make sure that I don’t overlook that I would like the workflow to stop which would be a signal for me to check table A for new member IDs (aka unmatched rows).

I’ve been doing a lot of research, but couldn’t find a solution. Can someone here help me with an idea on how to solve that?

I’d also be very grateful if someone knows a better way to signal that new members have been added to table A, instead of stopping the workflow.

Thanks a lot in advance.

Best,
Henan

[HansS]

Hi @Henan and welcome to KNIME Forum

When you join both tables with Joiner node, there is the option to direct unmatched rows (from table b) to a separate output port.With the Extract Table Dimensions node you can count the number of rows. If the number of rows = 0; then al records in table b matches records in table a. If not the workflow has to stop. To stop the worklfow I added an If Switch node. See stop workflow with if switch.knwf (40.0 KB)
Add some new ID’s to table b, and see how the workflow stops.

gr. Hans

[bruno29a]

Hi @Henan ,

@Henan

I’d also be very grateful if someone knows a better way to signal that new members have been added to table A, instead of stopping the workflow.

So, instead of terminating the workflow, you could actually send an email and let the workflow continue.

Something like this would work:

The first thing to configure here is the Joiner node. Make sure what’s highlighted are configured as such:

That way, the 2nd output port will show what records in Table A (Left) are missing in Table B.

The 1st output will contain all the matching records, from which you will be able to continue the workflow.

The other thing to configure is the Send Email node. First, the high level configuration (the automatic message):

You can enter the recipient email, subject, and even add how many missing MemberIDs from table B you have in the content.

After that, you need to configure the SMTP information, just like you would set up an email client like Outlook, or GMail, etc:

To test the workflow, I start with a scenario where all rows from Table A match in Table B:
Table A:

Table B:

Execution of the workflow:

2 things to look at here. You can see that the route from the IF Switch to the Send Email is disabled, and the Send Email node itself has no status, so it’s not run, which is expected since all rows match.

Also, another thing to look at, the “Continue process” (which would be other nodes that you may have to process the data) continues to run.

The next test is where we add new MemberIDs to Table A, but not to Table B. Here is the new Table A:

MemberIDs 4 and 5 have been added to Table A.

Table B remains the same as before.

Execution of the workflow:

We can see that the IF Switch to the Send Email is now enabled, and we can see the Send Email node at the Ready state (yellow) and ready to run (I did not run it cause I’d have to configure it, but the point is that it would run in this case), which is the expected behaviour.

We can also see that the process continued in parallel, which is what you wanted.

Here’s the workflow: Send notification if there are unmatched rows.knwf (21.9 KB)

EDIT: I originally thought that your process was being run as a job, hence why I added the sending of the notification. I just realized that it might not necessarily be the case , in which case you might not need these extra nodes. All you would need is just this:

Configure the Joiner as I explained, and simply check the 2nd output port of the Joiner (Right click on the Joiner, click on “Left unmatched rows”):

That will also show you what MemberIDs are missing:

And if you are afraid you might forget to check that port, you can leave one of the nodes there, which is the Extract Table Dimension:

You’ll just need to check the “Number Rows” row to see if it’s 0 or greater than 0. If it’s 0, then it means all rows in Table A matched in Table B. It basically shows the number of MemberIDs from Table A that did NOT match in Table B:

[bruno29a]

Hi @Henan , I came up with another way to notify you, basically with a popup message

Still with the previous example of 2 MemberIDs added to Table A but not to Table B, the workflow will produce this popup while continuing the process:

And of course, if all rows match, there will not be any popup.

I’ve added it to the workflow instead of replacing the email option, in case you want to use both. The updated workflow looks like this:

The Java Snippet looks like this:

It makes use of the JOptionPane.

The code is as follows:

if (c_Dimensions > 0) {
  String is_are = "are";
  String memberid = "MemberIDs";
  if (c_Dimensions == 1) { //plural or singular MemberID?
  	is_are = "is";
  	memberid = "MemberID";
  }
  String message = "WARNING:\n";
  message += "There " + is_are + " " + c_Dimensions + " missing\n";
  message +=  memberid + " in TableB";
  JOptionPane.showMessageDialog(null, message, "Missing MemberIDs", JOptionPane.INFORMATION_MESSAGE);
}

Most of it is just about building the message, but the basic logic is to popup only if the are missing rows from Table A to B.

Here’s the updated workflow: Send notification if there are unmatched rows.knwf (30.2 KB)

[Henan]

@HansS @bruno29a Thank you so much for your help, I’m very grateful for the time you invested!! I didn’t expect to get such detailed responses in such an extremely short time! It will take me some time to understand and try out your solutions, but I’ll reply asap.

[Henan]

@HansS @bruno29a Thank you again so much for your amazing help! I’ve tried all your solutions and all of them work like a charm!!

In fact, my workflow is a combination of both your ideas. It stops the workflow and sends an email to notify the user.

@bruno29a: I’ve got just a few questions regardings your worklow.

Email Solution: Shouldn’t I select “Merge join columns” in the Joiner node?

Popup Solution: Can you explain me why in the Row Filter node before the Java Snippet node you configured it to only include row 1?

And regarding the Java Snippet: where did you define “c_Dimensions”? Is c_Dimensions the number of rows after the applying the Row Filter node?

[bruno29a]

Hi @Henan ,

@Henan

In fact, my workflow is a combination of both your ideas. It stops the workflow and sends an email to notify the user.

Well, it depends on what you want to achieve. You mentioned that you wanted the workflow to continue, so I implemented it so that it can continue . If you wanted it to stop, I would have implemented it so that it would stop.

@Henan

Email Solution: Shouldn’t I select “Merge join columns” in the Joiner node?

First of all, the Joiner does not apply only to the Email Solution. The Email and Popup both occur after the Joiner. If you choose to merge, the unmatching values will be merged into the first output port. You will then have to do additional processing such as separating/filtering the unmatching values. By choosing the split, Knime will do the splitting of the unmatching values directly in the Joiner nodes and separate the matching into the first output node and the unmatching values into the second output node.

@Henan

Popup Solution: Can you explain me why in the Row Filter node before the Java Snippet node you configured it to only include row 1?

And regarding the Java Snippet: where did you define “c_Dimensions”? Is c_Dimensions the number of rows after the applying the Row Filter node?

The Extract Table Dimension node gives 2 information. Number of Columns and Number of Rows. I only care about the Number of Rows, which is in the first row (Row0). This will tell me if there were unmatching values. Also, at this point, you actually want to have only 1 row, as the Java Snippet will run as many times as there are number of rows.

The c_Dimensions is basically the Dimensions column that came the Extract Table Dimension. You can see it pointing to the Dimensions column in the Input variables. So, after the Row Filter, the Dimensions column has only the number of rows, so the variable c_Dimensions has the number of rows of unmatched records.

So, the popup will happen only if there are unmatched records (if (c_Dimensions > 0))

[Henan]

Thanks a lot again for the detailed explanation!!! Really appreciate it!!

ps: Too bad I can’t mark both @bruno29a and @HansS posts as solution. I chose Hans because he replied first, but actually all ideas work!