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

[#1]

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_CODE	HH_LAT	HH_LONG
01010	95.2 N	95.2 S
01001	94.1 N	93.2 S
01092	65.45 N	64.2 S
01610	122.1 N	120.1 S

Table 2

PD_5_ZIP_CODE	PD_LAT	PD_LONG
01606	120.3 N	119.2 S
10001	119.10 N	120.2 S
01510	110.20 N	123.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_CODE	HH_LAT	HH_LONG	PD_5_ZIP_CODE	PD_LAT	PD_LONG	Distance (miles)	Time (Hrs)
01010	95.2 N	95.2 S	01606	120.3 N	119.2 S	to be derived	to be drived
01010	95.2 N	95.2 S	10001	119.10 N	120.2 S
01010	95.2 N	95.2 S	01510	110.20 N	123.2 S
01001	94.1 N	93.2 S	01606	120.3 N	119.2 S
01001	94.1 N	93.2 S	10001	119.10 N	120.2 S
01001	94.1 N	93.2 S	01510	110.20 N	123.2 S
01092	65.45 N	64.2 S	01606	120.3 N	119.2 S
01092	65.45 N	64.2 S	10001	119.10 N	120.2 S
01092	65.45 N	64.2 S	01510	110.20 N	123.2 S
01610	122.1 N	120.1 S	01606	120.3 N	119.2 S
01610	122.1 N	120.1 S	10001	119.10 N	120.2 S
01610	122.1 N	120.1 S	01510	110.20 N	123.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?

[#2 · Accepted 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

[#3 · Accepted 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. 

[#4 · Accepted solution]

It worked as you suggested.

[#5 · Accepted solution]

Thanks  Byron_JMP.