topic check if data exist in another table in Discussions

check if data exist in another table

joann — Fri, 09 Jun 2023 22:08:09 GMT

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.

Re: check if data exist in another table

Dan_Obermiller — Wed, 10 Mar 2021 22:53:02 GMT

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?

Re: check if data exist in another table

brady_brady — Thu, 11 Mar 2021 02:11:46 GMT

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

Re: check if data exist in another table

joann — Thu, 11 Mar 2021 15:24:49 GMT

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?

Re: check if data exist in another table

jthi — Thu, 11 Mar 2021 15:32:42 GMT

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?

Re: check if data exist in another table

joann — Thu, 11 Mar 2021 16:38:30 GMT

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?

Re: check if data exist in another table

brady_brady — Thu, 11 Mar 2021 17:39:16 GMT

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;

Re: check if data exist in another table

joann — Thu, 11 Mar 2021 18:35:47 GMT

It works! Thanks so much Brady!