Hi everyone.
i am joining 2 tables , in order to check that the join was on a primary key i.e. unique I would like to do a test like in Alteryx where the number of records that from the left are equal to (Joing and left) to ensure that no additional records have been created because of this.
I would like the execution to stop if its not.
Thanks,
Thomas
just to clarify. I want to make sure that what is going to the join is coming out - either as Joined or not joined. Given that left outer + inner should not be greater than the number of records in the left table.
I managed to get the some of both my getting the record count and put in them in the same table… all I need to a comparison if the totals dont match it fails (couldnt manage with breakpoint)
Hi @Thomas1988 , if you’ve managed to get the totals already, and you just need to create a means of terminating the process, you can use Java Snippet if your counts are in a table (or Java Edit Variable if your counts are in flow variables)
Here is a mock up I did
The java snippet just contains a condition and throws Abort() with a message which halts processing beyond that point.
For something slightly more fancy, if you are able to install the nodepit power nodes, I have a component called “Abort on Rowcount”, which you could use (or use as the basis for your own if you wish). This changes the annotation of the component if it aborts.
Configure it with flow variables to define to define the count that it should match to:
Check join rowcount.knwf (116.3 KB)
thanks, can’t try the addons as they are blocked on my network.
Weirdly the java snippet is only working when the values are equal (the opposite of what i need)
Hi @Thomas1988, what’s strange to me about this is not so much the behaviour on your system, but that it is inconsistent with mine.
Technically my java code was poorly written because it was using the basic inequality operator != even though these are Integer Object values being compared, rather than primitives (int).
In that respect, to avoid the kind of problem I suspect you are facing, the comparison should really be done with the .equals() method rather than !=. So that’s my bad, and my code should probably have been done differently.
What happens if you replace your if statement with the following?
if ( !(c_TotalRecords.equals(c_Total)) )
{
...
}
If it’s not that, I’m at a bit of a loss as to what is happening.
By the way, instead of
System.out.println("message")
you can write to console with:
this.logWarn("message");
there are also others to output according to the minimum log level you have specified in KNIME settings:
HI Brian, it needed resolved my issue!!! quite a strange one!!!
on another note how view the log files in the workbench. I can see the the abort (i just changed it to equal rather than not equal just to try out the log)
if ( (c_TotalRecords.equals(c_Total)) )
{ this.logError(“Unmatched”);
throw new Abort(
Integer.toString(c_Total) + " (" + c_Total + “) Mismatch " + Integer.toString(c_TotalRecords) + " (” + c_TotalRecords + “)”
);
}
Hi @Thomas1988 , glad you got it working. The whole object thing with java can seem odd, but the use of != is literally asking hava if the c_TotalRecords object is the same as the c_Total object (i.e. does it occupy the same address in memory), so it is ok to use with primitive datatypes such as basic int variables, but
but shouldn’t be used to check equality of actual Objects e.g. full blow Integer objects which is what c_TotalRecords and c_Total are.
Of course the challenge is that because of the way java snippet handles the creation of objects etc behind the scenes, it isn’t immediately apparent that these are Integer objects and not primitive int datatypes. So I am still curious why it worked at all with my code, when I used it. I’ll try to investigate that further at some point because something doesn’t feel quite right there. So yes it is quite odd!
Re viewing the console, the console viewer is obviously not (yet?) available directly as a window in Modern UI, but there are some workarounds which I have discussed in the another thread (linked here) which may be of interest.
