Joining challenge

I have two tables that I need to join.

One contains values from an analysis:

country code source postcode source country code destination postcode destination
CH 01 DE 93
CH 02 DE 94
CH 03 DE 99
CZ 01 DE 01
CZ 02 DE 01
CZ 03 DE 01
CZ 04 DE 01
CZ 05 DE 99
DE 01 AT 01
DE 02 AT 02

The other contains master data in which I must find a match in order to retrieve a special code:

country code source postcode source country code destination postcode destination value to retrieve
CH alle DE 93 D
CH alle DE 94 D
CH alle DE 99 D
CZ 01 DE 01 C
CZ 05 DE 01 C
DE Alle AT Alle C

Now my problem is that the master data is not as granular as the input but contains a placeholder “Alle” that includes all possible values.

I need to match all 4 columns in order to get the last column as result.
How can I match these two?

Thanks.

If I understand you properly, you need to filter 2 groups from master table one with Alle and other without. For first group do a cross join to the analysis table and for the second use regular join. Combine the result of joins with Concatenate node.

5 Likes

Actually, both groups in master table can have “alle”.

Hi @gentile

Make one additional step on top of what @izaychik63 suggested. Split your masterfile in 4 groups (alle-alle), (… - alle), (alle - …) and the rest. Inner join each group on 2, 3 or 4 columns out of your analysis file and concatenate the results together.
You could use e.g. a Rule Engine node to create an additional column assigning the group and four Row filter nodes to split the masterdata on this new column. Or four Rule-based row filter nodes to get this done wihout adding a new column.

2 Likes

Sorry, I’m having a hard time to align the two suggestions. Guess I’m missing something.

Here’s the workflow with the suggested Rule Engine Node and Row Filter nodes:
joiner-challenge.knwf (1.6 MB)

Can you kindly advise how to take this further?

Thanks @izaychik63 and @JanDuo for your help.

I think I made it. It’s not pretty, but it should do the trick:
joiner-challenge.knwf (1.6 MB)

In case you (or anyone else) knows a more elegant way to solve this, please let me know.

According to me there is a simpler solution but I have to ask uou a question:

in analysis table (in workflow you posted) there are a lot of records without destination data.
What does it mean? Do you wantjoin these records with all records in master table that have the same source data (or Alle) independently destination data contained in them?

Thanks @pigreco.

In an ideal world, all records would have destination data. They will at some point, but I don’t have them yet. So, for my join at the end these empty records will not be considered (which is fine for now).

But yes, basically I need to join all 4 columns with each other in order to get the one column “value to retrieve”.

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