Comparing 1 file to other and returning the results

[Sanju04]

Hi,

I have 2 data set, one with data and other having rules with comparator like AND , OR. I need to check each row of data set if it matches to any of the rules. If yes then return the value from Match Found column.

data set sample-

Key	Name	Value1	Value2
123	N1	V11	V21
234	N2	V12	V22
345	N3	V13	V23

Rules data set

Rule No	Value1	Comparator	Value2	Match Found
1	V13	AND	V23	Possible
2	V12	OR	V21	Possible
3	V11	AND	V22	Possible

I have tried using Join tool which works only for comparator AND because in JOIN tool I am giving both Value1 and Value 2 columns to be matched. but not able to figure out how to work out the OR comparator.

Any ideas or suggestions on this?

Thank you.

[jarrod]

Are you saying for AND Comparators both value 1 and value 2 have to match? So for rule 1 you only want scenarios where value1 is V13 and value2 is V23?

then for rule 2 (OR) you want records where value1 is V12 OR value2 is V21?

in this case, i would set up two sets of joins. the first join will have the AND comparator rules that join to both Value1 and Value2 fields. Then the second stream i would set up two joins, one where value1 is joined and the other where only value 2 is joined. then you can just summarize the Key values and pull those from the original set (otherwise you get more than one match for records that have BOTH criteria met on the OR rules)

Here's what i was thinking:

[rbarco · Accepted solution]

Hi @Sanju04 

I think you need to add 2 more joins to comparing OR rules.

I've attached a possible solution.

If this solves your issue please mark the answer as correct, if not let me know!