Hi All, Hoping to pick you brains on best approach Aim : For given ID I need to check if the values match and if its matches then then flag them as "Yes" if not equal then "No". Currently : I have two data inputs, in both Inputs i have two columns (Id & ,Name), I have union'd the two data sets together Input A IDName11111Masond322222Apple Inc Input B IDName11111Masond3022222Apple Inc Union Output IDName11111Masond311111Masond3022222Apple Inc22222Apple Inc Expected outcome IDNameName Match11111Masond3 11111Masond30No22222Apple IncYes22222Apple Inc

Are values within a group equal

Hi All,

Hoping to pick you brains on best approach

Aim : For given ID I need to check if the values match and if its matches then then flag them as "Yes" if not equal then "No".

Currently : I have two data inputs, in both Inputs i have two columns (Id & ,Name), I have union'd the two data sets together

Input A

ID	Name
11111	Masond3
22222	Apple Inc

Input B

ID	Name
11111	Masond30
22222	Apple Inc

Union Output

ID	Name
11111	Masond3
11111	Masond30
22222	Apple Inc
22222	Apple Inc

Expected outcome

ID	Name	Name Match
11111	Masond3
11111	Masond30	No
22222	Apple Inc	Yes
22222	Apple Inc

Common Use Cases

Data Investigation

Accepted answers

Christina_H

Updated workflow attached that will handle multiple columns. I've left the output transposed - it can be cross-tabbed to the output you've provided, but the table will get wider and harder to read with every new attribute you want to compare.

I've also removed the rule that took the majority version of the name (or address/website), the "correct" version will be from the earliest input. Depending on your data, you might want to update this to take the earliest non-null value?

Union match.yxmd

All comments

Christina_H

How about a distinct count of names?

Masond3

@Christina_H i thought about that option, however i am thinking of scalability.

At the moment we have two inputs(this potentially going to expand to 10). It is possible that an ID, wont be in the other 9 sources, so doing a distinct count, i dont think would work ? ( could be mistaken though)

Christina_H

What do you want to happen if the ID is missing from some of the sources?

In this version, if the ID isn't in one of the sources it would still be labelled as True (there's exactly one variation of name for that ID).

Masond3

@Christina_H i think in this scenario, i cant evaluate it against another record, thereforei think the "Name match" would remain blank

The overall goal, is to flag records where this mis-matches, ( therefore i think there would need to be a minimum of two in a group ie iD)

Christina_H

OK, how about adding a regular count as well as the distinct count? Name Match can then be reset to null if count=1

Rana_Kareem

Hi @Masond3 ..

You can try this method.

Hope it helpful..

Masond3

@Christina_H I think you are along the right lines. so the way i see the "Name match" in this scenario is that its flagging a group, is there a way on top of this to call out exactly row would be mis-matched within that group,( if i indicated what input trumps input, ie input A always supersedes input B. so in the scenario of masond3 vs Masond30, Masond30 would be the one which is incorrect, as it should align to masond3

Christina_H

So something like this?

Masond3

@Christina_H 100% thats the exact outcome i would want !can you share your wf?

Christina_H

Workflow attached. For more inputs this would take the majority name, or the name from the earliest input in case of a tie.

Union match.yxmd

Masond3

@Christina_HThis gets me out of a jam, so i can least proceed.
Do you know if there a way where where given the input you could set the rules ?

Christina_H

The rules to select the correct name are in the sort tool, the following unique tool takes the first name for each ID. So to change the rules you would need to adjust the sort.

What kind of rules were you thinking of?

Masond3

So i am thinking that "Input A", is my "golden version of the Truth", therefore any other Input .i. B,C,Z etc, i want to compare those values against "Input A"

Now for scalability there also be other attributes that i would need to compare.

Example,
Address,
Website,

ID	Input	Name	Website	Address	Name Match	Website match	Address match
11111	A	Masond3	www.masond3.ac.uk	1 High St		Yes	Yes
11111	B	Masond30	www.masond3.ac.uk	1 High St	No	Yes	Yes
11111	C	Masond3	www.masond3.ac.uk	1 high Stree		Yes	No
11111	D	Masond2	www.masond3.ac.uk	1 High Street	No	Yes	No

Christina_H

Union match.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703