check if data exist in another table

[#1]

Hi, I have 2 tables A and B, each has a column "email" and "id". I want to add a new column in table A, if that row has "the same email OR same id" showing up in table B, then "YES", if no then empty. Could someone help me how to write this script? 

Thank you.

[#2]

I am not sure what you are looking to do.

Suppose this is Table A:

and this is Table B:

So, consider user1 in row 1 of both tables. There are two different id's. Which one should be kept? Or should we see that person listed twice? Similarly, what happens with id=84? Should there be two lines or should it have the user7 email address?

[#3 · Accepted solution]

Hi, if I interpret your question correctly, you want a YES in table A whenever the e-mail or ID appears in B, regardless of the location in which these appear in table B. If so, this will do:

Names Default To Here(1);
dtA = datatable("tableA");
dtb = datatable("tableB");

//make lists of IDs and emails in table B
bIDs = dtB:ID << get values;
bEmails = dtB:email << get values;

//use formula to check if anything in the given row of A occurs anywhere in B
dtA << new column("bMatch", character, formula(if(contains(bIDs, :ID) | contains(bEmails, :email), "YES", "")));
dtA:bMatch << Delete Formula;

Here is sample output:

Cheers,

Brady

[#4]

Thank you! This is what I'm looking for.

I have one error with this script. Wonder if it's because there's an underscore within the col name?

[#5]

To my understanding _ shouldn't cause issues (at least not with JMP14.3 and JMP15.2). Are you sure you have  Email_HASHED column in your dtB datatable?

[#6]

Sorry my bad! Wrong column name, i fixed it and it worked! Thanks so much!
One following question- if in table b there's another column like country, which contains US or CA. Could I do the same YES column in table a but only when table b country = US?

[#7 · Accepted solution]

Yes, you can. I think the easiest way to do this is to restrict what you put in the lists in the first place, by filling the lists with only values that occurred in rows where country == US. See the new line below the first comment, as well as the [r] index used in the next 2 lines.

Names Default To Here( 1 );
dtA = Data Table( "tableA" );
dtb = Data Table( "tableB" );

//make lists of IDs and emails in table B
r = dtB << get rows where( :country == "US" );  //restrict rows to those where country is US
bIDs = (dtB:ID << get values)[r];
bEmails = (dtB:email << get values)[r];

//use formula to check if anything in the given row of A occurs anywhere in B
dtA << New Column( "bMatch",
	character,
	formula( If( Contains( bIDs, :ID ) | Contains( bEmails, :email ), "YES", "" ) )
);
dtA:bMatch << Delete Formula;

[#8]

It works! Thanks so much Brady!