nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

check if data exist in another table

joann

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.

Accepted answers

brady_brady

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

brady_brady

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;

All comments

Dan_Obermiller

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?

brady_brady

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

joann

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?

Screen Shot 2021-03-11 at 10.22.42 AM.png

jthi

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?

joann

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?

joann

It works! Thanks so much Brady!