Subscribe Bookmark RSS Feed

Merging two data tables while "intentionally" duplicating records for one against the other table.

jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

heHi All,

I am trying to work on a project. I have two different data tables with uneven rows.

 

Let say, Table 1 has 19 rows of data with 3 columns and Table 2 has 50 rows of data with 3 columns.

 

For simplicity, let assume the following:

 

Table 1

HH_5_ZIP_CODEHH_LATHH_LONG
0101095.2 N95.2 S
0100194.1 N93.2 S
0109265.45 N64.2 S
01610122.1 N120.1 S

 

 

Table 2

PD_5_ZIP_CODEPD_LATPD_LONG
01606120.3 N119.2 S
10001119.10 N120.2 S
01510110.20 N123.2 S

 

What I would like to join both tables (1 & 2) with the destination table being Table 1. In this case, I would like all data from Table 2 to be paired with each of unique record or row in table 1.

For example, the resulting table should look like this.

 

Table 3

HH_5_ZIP_CODEHH_LATHH_LONGPD_5_ZIP_CODEPD_LATPD_LONGDistance (miles)Time (Hrs)
0101095.2 N95.2 S01606120.3 N119.2 Sto be derivedto be drived
0101095.2 N95.2 S10001119.10 N120.2 S  
0101095.2 N95.2 S01510110.20 N123.2 S  
0100194.1 N 93.2 S01606120.3 N119.2 S  
0100194.1 N93.2 S10001119.10 N120.2 S  
0100194.1 N93.2 S01510110.20 N123.2 S  
0109265.45 N64.2 S01606120.3 N119.2 S  
0109265.45 N64.2 S10001119.10 N120.2 S  
0109265.45 N64.2 S01510110.20 N123.2 S  
01610122.1 N120.1 S01606120.3 N119.2 S  
01610122.1 N120.1 S10001119.10 N120.2 S  
01610122.1 N120.1 S01510110.20 N123.2 S  

 

In this file, HH stands for Household and PD stands for Program Destinations (our products). Thus, we want to market programs that are located certain distance away from the homes of our customers per time travel (let say, 1 hours away, 3 hours away and 5 hours away). Using the household zip code or Lat or Long and the Program destinations zip code or Lat & Long I can calculate the total distances and time that any one of our programs is away from the home of any one of our customers. The expectation is that I will come up with a number of programs that are 1 hour away, 3 hours away, or 5 hours away from each of our households.

 

So, to make this easier, I would like to create one file in which the unique HH Zip Codes with Lat and Long for each household is replicated against each of the program destinations zip code with Lat and Long; such that, I can use a script to calculate the distance and time.

 

I want the final table to like Table 3 above. Any trick how to do this in JMP 13?

Jenkins Macedo
4 ACCEPTED SOLUTIONS

Accepted Solutions
Byron_JMP

Staff

Joined:

Apr 26, 2012

Solution

I really hope you weren't like me and resorted to scripting before you found this option in the Tables/Join platform.

 

Tables>Join>Matching Specification = Cartesian Join

 

 

jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

Solution

Lol Byron_JMP. Trust me. One thing I like about JMP is my ability to avoid scripting...lol. Less scripting for me means doing more. I thought about using Cartesian Join. Perfect. I will try it and see if it works and keep you posted. 

Jenkins Macedo
jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

Solution

It worked as you suggested.

 

Sample.jpg

Jenkins Macedo
jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

Solution

Thanks  Byron_JMP.

Jenkins Macedo
4 REPLIES
Byron_JMP

Staff

Joined:

Apr 26, 2012

Solution

I really hope you weren't like me and resorted to scripting before you found this option in the Tables/Join platform.

 

Tables>Join>Matching Specification = Cartesian Join

 

 

jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

Solution

Lol Byron_JMP. Trust me. One thing I like about JMP is my ability to avoid scripting...lol. Less scripting for me means doing more. I thought about using Cartesian Join. Perfect. I will try it and see if it works and keep you posted. 

Jenkins Macedo
jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

Solution

It worked as you suggested.

 

Sample.jpg

Jenkins Macedo
jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

Solution

Thanks  Byron_JMP.

Jenkins Macedo