cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
joann
Level IV

create new id to deal with multiple to multiple relationship

*Update

Sorry I should be more clear on the question. The data i currently have looks like:

id    email

1     a

1     b

2     a

2     c

3     d

We can see that id 1 2 and email a b c are actually the same person because the info overlaps. 

What I'm trying to do here is to come up with a match table with a new key which i can use to match back to the original table to identify those overlap ones are actually the same person:

newID   id/email

111        1

111        2

111        a

111        b

111        c

222       d

The newID doesn't need to be in a specific form, it can even equal to the original id, as long as we can use it to identify a "unique person".

-------------------------------------

Hi, I have data with 2 columns, one is individual id, and one is email. The problem with this data is that the relationship between individual id and email is multiple to multiple, meaning one individual id could has multiple emails, and one email could have multiple individual ids. Thus i’d need to create a mapping table of a new unique id to all individualID/email, which specifies that those individual ids or emails if any overlap are actually the same person. I couldn’t really think of a way to do it without script. Could anyone help? Thank you!

 

 

22 REPLIES 22
joann
Level IV

Re: create new id to deal with multiple to multiple relationship

Hi Jarmo, the result seems to be the same as previous one, as attached screenshot. The "newid1" is the one using previous script, and the "newid" is the one using your latest script, which same as previous one, assigned those different individual ids with empty email the same newid "750". Could you please check? Thank you!

 

Screen Shot 2021-03-09 at 3.56.12 PM.png

jthi
Super User

Re: create new id to deal with multiple to multiple relationship

I did read/understand incorrectly how you wanted to handle the missing values. You want unique value to each of them, right? Because missing values are handled last, you can just replace last rows after //handle missing with the script below:

//handle missings
For Each Row(dt,
	If(IsMissing(:newid),
		index++;
		dt:newId = index;
	);
);

-Jarmo
joann
Level IV

Re: create new id to deal with multiple to multiple relationship

Thank you soooo much Jarmo! It works!!