Joiner Configurations in a Component

I need to create a component that contains a joiner and provides in its configuration the ability to match up one or more join columns for the joiner. Any pointers?

Hello @iankruger,

you can try following. Use 2 Column Selection Configuration nodes inside component and choose you joining columns. This node will provide chosen columns as flow variable which you can use to control Joiner.

Br,
Ivan

5 Likes

This works well (as per your joiner in a loop post) if the joiner columns in the two tables have the same names but I struggling with how to use this to connect two differently name fields especially if there is more than one join column?

1 Like

Hello @iankruger,

I see. For Joiner to work like this you’ll have to modify your column headers to be same. Maybe you can use this workflow as inspiration :wink:

Give it a try and if you’ll have issue I can give it a try. Sounds like fun :slight_smile:

Br,
Ivan

3 Likes

Ok, I have been trying but I am seriously stuck. Maybe I am trying to do too much by putting the column rename into the same component as joiner?

@iankruger

I solved this by using Lists as the leftTableJoinPredicate and rightTableJoinPredicate in the joiner. Simplified version of my use case where I query against a database (representing the right hand side):

  • First create a list of the input columns from your workflow that you want to have in the lefthand side of the joiner.

image

  • Create a variable that contains a comma seperated string of all the values that eventually shoud populate the right hand side (in my use case it’s eventually a concatination of the prefix + val’s).

  • Convert this also to a list.

image

  • Pass the list variables to the joiner.

image

The left and right predicate are different in my case but the joiner still executes the left outer as expected.

Off course, the data is passed along while going through all these steps to actually perform the joiner. My example is now fixed to two columns whereby the righthandside is also fixed for the equivelant of these. But theoretically you can make this dynamic by letting the user define the list in the component config.

I do something similar for optional fields I want to query by added a string input in the component, that is also passed as variable ($${SConfigurationAddOptionalFields}$$) to the DB query reader and included in the data upon a joiner match.
image

image

Hopefully this helps a bit.

Regards,
Arjen

2 Likes

This looks great but I am struggling to follow how you set up the whole workflow from your pictures, would it be possible to share the workflow or the componant, please?

It’s company property in active use for customers so sharing it “as is” won’t be possible unfortunately. I’ll check if I can make dummy clone of it.

That would be fantastic if you were able to do a dummy. To be clear what I aiming for in the componant is the configuration that:

  1. selects the fields that need to be joined (one or more) from the left and connects them to their equivalent in the right table. The rest of the fields (the unjoined ones) from left and right need to be included in the joined table
  2. runs the joiner in the componant as part of a workflow (which analyses the unmatched tables)

My go at a solution based on the above suggestions. Improvements most welcome
JoinerReportComponent.knwf (61.3 KB)

1 Like

Some notes:

When I select two equal columns, the joiner should work right? I tried a lot of combinations of columns and the components always returns no result here because of the If switch.

Only when I changed the first Rule Engine from FALSE to TRUE it started to work. If I understand it properly, you want to halt the flow whenever no column is given in the input because in the second rule engine you set that it should go top when the missing count is >0.

Also, the joiner uses the same variable for the RHJoinColumns twice rather than the LH.

image

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