HELP: How do I check the combination/permutation in a subset data is correct?

[charles7yau]

I have a subset of data that has various field. I need to make sure that my combination o field A-E in row 1, 2 and 3 matches to the master data.

What tool should I be using?

	Subset Data
	Field A	Field B	Field C	Field D	Field E
1
2
3

	Master Data
	Field A	Field B	Field C	Field D	Field E
1
2
3
4
5
6
7
8
9
10

Thanks!

[mceleavey]

Hi @charles7yau ,

you could use the join tool and join on all fields you're trying to match.

Everything that comes off the J output matches.

If you post some data we can show you.

M.

[charles7yau]

Thank you!

I have uploaded a sample.

First Tab: Subset

Second Tab: Master Data

[apathetichell]

Are you matching columns or data? Two sample data files will probably make a better illustration of how to match two datasets.

Field info both sets and join on name  and you can see that columns that aren't in both sets  will be under the right and left anchors

[charles7yau]

Hi,

Thanks for the information.

I am matching the field combination in the row. 

For eg row 1: Is the combination of account, cost center, entities, bank and description matching to the master file.

	Account	Cost Center	Entities	Bank A/C	Description
1	222022	11011	SG	DBS	Cash

	Master File
	Account	Cost Center	Entities	Bank A/C	Description
1	222022	23001	US	BOA	Cash
2	222022	11011	SG	DBS	Cash
3	221102	10001	US	BOA	PPE
4	221655	11011	AU	CBA	AP
5		10001	AU	DBS	AR
6	408881	23001	AU	CBA	AR
7	603351	23001	AU	CBA	OPEX
8	650003	33001	AU	CBA	OPEX 2
9	650004	33001	SG	DBS	REV
10	671167	11011	US	BOA	REV 2

Thanks,

[Maskell_Rascal]

@charles7yau Are you attempting to validate an entry matches against a master file? If so, you can do this as both @mceleavey and @apathetichell have recommended with the Join Tool. Just specify the fields you want to match, and everything in the J output is what could be found. 

Kinda like this:

Cheers!

Phil

[charles7yau]

Hi,

Is there a way not to join the data, but to add a column at the back and say Yes it matches of No it doesn't?

Something like that

	Subset File
	Account	Cost Center	Entities	Bank A/C	Description	Match?
1	222022	11011	SG	DBS	Cash	Y
2	221655	10001	AU	CBA	AP	N
3	320206	10001	US	BOA	AR	N

[apathetichell]

Yes. and if you post two sample files @Maskell_Rascal @mceleavey or I could readily build a workflow to explain how to do this.

[danilang]

Hi @charles7yau 

The records that come out of the J anchor on Join are those that match on both sides of the input.  The records on the R and L anchors are the records from the R and L inputs that don't match.  You can use this to add in a match flag.  Just add a Formula tools with a Match? field with "Y" on the J anchor and another one for "N" on the L output

Union and sort and you have what you're looking for

Just make sure that the records in your master file are unique, or you'll have duplicate records coming out the J anchor

Dan