Identify comparable entries in two datasets

[raj8257]

I have two lists - 'List 1' and 'List 2'. List 1 is the total dataset and it contains all the entries in List 2. Is there a simple way of indicating which entries in List 1 are also included in List 2 by comparing certain criteria?

List 1					List 2
Date	Type	£			Date	Type	£
01/01/2020	Apples	1.00			01/01/2020	Apples	2.00
01/01/2020	Apples	2.00			02/01/2020	Apples	1.00
02/01/2020	Pears	3.00			03/01/2020	Pears	4.00
02/01/2020	Pears	4.00			04/01/2020	Bananas	2.00
02/01/2020	Apples	1.00
02/01/2020	Apples	2.00
02/01/2020	Bananas	2.00
03/01/2020	Pears	3.00
03/01/2020	Pears	4.00
03/01/2020	Apples	1.00
03/01/2020	Apples	2.00
04/01/2020	Bananas	2.00

I want to match the categories - Date,Type AND £ and highlight in List 1 the entries from List 2

Desired output
List 1			Entry included in List 2
Date	Type	£
01/01/2020	Apples	1.00
01/01/2020	Apples	2.00	Y
02/01/2020	Pears	3.00
02/01/2020	Pears	4.00
02/01/2020	Apples	1.00	Y
02/01/2020	Apples	2.00
02/01/2020	Bananas	2.00
03/01/2020	Pears	3.00
03/01/2020	Pears	4.00	Y
03/01/2020	Apples	1.00
03/01/2020	Apples	2.00
04/01/2020	Bananas	2.00	Y

For context, actual List 1 contains 35k entries and List 2 contains 25k entries.

I've tried the 'Join' tool but it only results in the matched entries only. I still want the full data from List 1 - ie not just matched entries. I also tried just joining by a single category (eg type) but that results in a very high number of outputs (35k x 25k)

I tried the 'Union' tool but that just adds List 2 entries to the bottom of List 1. Not what I wanted.

I was thinking of using the Formula tool - ie IF List1 date = List2 date AND List1 type = List2 type AND List1 £ = List2 £ THEN "Y" ELSE "" - but Formula tools only accept single inputs. I don't really want to combine the data, just highlight the relevant entries in List 1

[DeanWest]

@raj8257 

Here is a proposed solution. You could perform a left outer join in order to achieve the sample output you provided.

In order to perform the left outer join, you would just need to join by the three fields and then unionize the "L" (Left/Left Unjoin) output anchor and the "J" (Join/Inner Join) output anchor. Please refer to the snippet and example workflow attached below:

Please let me know if you need any more assistance 🙂

[Qiu]

@raj8257 

How about a little out of box thinking here.

[raj8257]

Hi @Qiu Thanks for the response. The actual dataset includes a number of columns to add together. I'm trying to add a numerical value like in the example to a number of text strings, but when I add the value the formula doesn't work

I'm guessing it's because the data type is string even though i'm adding in a number value but I'm not sure.

[Qiu · Accepted solution]

@raj8257 
Because the "+" can only work with string type of Data fields, and we need to convert those numeric fields before concatanating them.

I modified the workflow now to be more dynamic to adapt the number of columns changes.

[raj8257]

Perfect. Thank you!