Join on every row, not the first only

[romanzdk]

Hello, I have table like this

ID	JoinKey	Some other detail
2	ICT3	foo
3	ICT3	foo
4	ILM1	foo
5	ILM2	foo

I need to join it with this table

ID	JoinKey	Value
1	ICT3	20
2	ILM1	10
3	ILM2	30
4	ICT2	50

What is the actual result I get:

ID	JoinKey	Some other detail	Value
2	ICT3	foo	20
4	ILM1	foo	10
5	ILM2	foo	30

The result I need:

ID	Joinkey	Some other detail	Value
2	ICT3	foo	20
3	ICT3	foo	20
4	ILM1	foo	10
5	ILM2	foo	30

So basically my Join joins only first occurence of the JoinKey but not the others. I use regular Join tool with Join by Specific Fields configuration by JoinKey.

How can I solve this please?

[Thableaus]

Hi @romanzdk 

It seems you're using "ID" as a Key Field, and for your case, you should just use the "JoinKey" field to get the expected results.

Check your Key Fields in the Join tool again, that might be the problem.

Cheers,

[romanzdk]

I use JoinKey for the joining tool

[Thableaus]

@romanzdk 

If that's the case, I recommend you to check leading and trailing whitespaces in your JoinKey field.

The Join Tool requires an exact match - it's also Case Sensitive, so, double check that on both datasets.

Cheers,

[romanzdk]

Data are cleaned but it looks I have to deal with Many-To-Many relationship.

[TonyA]

I copied your data and did the join. I got the result you said you were expecting. I'm attaching my workflow. I suggest you compare it to yours and see what's different.

[romanzdk · Accepted solution]

Thank you all very much. Problem was I had in first table some keys like "ICt3" and in the second one were all "ICT3" so the case was the case.

Thank you all again.