Identify which fields did not match in a join

[aka_ash]

Hi,
I am building a workflow that has a join looking at matching multiple fields from the 2 sources.

Wanted to know if there is a way I can identify which fields in the left and right outputs did not match and remain unjoined.

Is this possible to do?

Thanks in advance.

[mceleavey]

Hi @aka_ash ,

That's what the left and right outputs are. 

The left output is those items from the left input that did not match, and the right output is those form the right input that did not match.

M.

[aka_ash]

Appreciate the quick response @mceleavey .

I realised i forgot to mention that i am using the Join by specific fields option.

I can see some items that match all the specified fields and can see the rows that did not meet all the specified fields but they do match some of those fields.

Is there a way to highlight which of the specified fields it did not meet?

Thanks,

Ash

[mceleavey]

Ah, I see. You are joining on multiple fields and want to know which one of the fields they fail on.

Can you give me an example of your dataset and I'll look into it for you.

M.

[aka_ash]

I have attached a couple of test source files.

Scenario: Looking for matches for Column [1-10] with Fields [1-10]

Some rows will match/join, some will be unjoined as they do not meet the match the corresponding fields

Thanks again,

Ash

[mceleavey]

Hi @aka_ash ,

ok, so I'm not sure how you're trying to join as I need more information, but I assumed you're trying to join Field 1 to Column 1 and row to row?

If that's the case, then the solution attached will give you what you want. The data is pivoted to determine which fields join and which don't. The results are as follows:

It's difficult to know what you need as I don't know what matching you're trying to do, and the example data is a 1 to 1 match.

M.

[aka_ash]

Apologies, my testfile 1 must have autosaved

I have attached the updated test files and a workflow

Example Expected output in a field for each row or something similar

Mismatched fields : Column1, Column5

[bhrmitra]

Hi akaash,

Hope I have understood your problem statement correctly. The below screenshot shows the field names that did not join.

If this solves your issue please mark the answer as correct and also hit the like button, if not let me know! I've attached my workflow for you to download if needed.

Thanks,

Abhra Mitra

[aka_ash]

Hi @bhrmitra,

Thanks for posting that workflow. That is helpful.

Unfortunately my production data will have thousands of rows and would prefer to keep it inline with the input data.

I have screenshot of an example i am hoping to achieve, highlighted column displaying which item did not match .

Is something like the below possible?

Thanks,

Ash

[bhrmitra]

Hi,

Please have a look at the below solution. I guess this is what you were asking

If this solves your issue please mark the answer as correct and also hit the like button, if not let me know! I've attached my workflow for you to download if needed.

Thanks,

Abhra Mitra