Hi! I hope you can help me. I have to look up from another data source and if there is a multiple match from the data source, it should show “Unknown Supplier” instead of getting the match result. How should I do it? Do I use a rule engine node or a column expression node and how can I write the expression? OR is the joiner node don’t get the match result if there are multiple match or is the joiner node gets the first occurrence of the match? Thank You and I appreciate this community.
Hi @Heldyyyyy, if you are getting more than one match, then it would mean that the data is duplicated in the lookup table, based on the criteria with which you are trying to join, so if you want those to be treated as “Unknown Supplier”, you could achieve this as follows:
Pass your supplier table into a Duplicate Row Filter, and filter based on all the “join columns” you will be using in your joiner.
On the advanced tab of the duplicate row filter specify the following:
Take the output of the Duplicate Row filter into a Row filter, and specify the following, to retain only non-duplicated rows:
Join the main table as the top input to the joiner, and the lookup table as the lower input.
Specify keeping matching rows and Left unmatched rows
Where there is missing supplier data, use a Missing Values node to set to “Unknown Supplier”
Mark duplicate joins as unknown.knwf (16.0 KB)
4 Likes
If I understand correctly then I believe the approach that I would take is to use 2 separate Joiner nodes. 1 joining Left only, and 1 joining center only. (Or split the output columns of a single Joiner) Then use the column expression or rule engine after the center only to set the Unknown Supplier, then concatenate the 2 together.
@takbb beat me. Missing value is probably easier as well. This approach might be useful if you had more extensive changes to the duplicates.
3 Likes
Hi @takbb thank you, this works dynamically. I was thinking first of joining 2 tables and rule engine and concatenate them but your workflow works great. Thanks again for your help!
Hi @iCFO this method also works great, I actually thought of joining tables but I just can’t pinpoint the next step 
, I also think the first one was more easier to navigate 
. Thank you for your input, I appreciate it!
1 Like
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.