check if data exist in another tableHi, 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.
AnalyticsScriptingRe: check if data exist in another tableI 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 tableHi, 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 tableThank 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 tableTo 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?
