366915_en-US

liliconcom

check if data exist in another table

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.

Automation and ScriptingConsumer and Market ResearchMass CustomizationQuality and Process EngineeringRe: check if data exist in another tableIt works! Thanks so much Brady!Re: check if data exist in another table

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 tableSorry 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

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

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

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

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?