Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Iterating Through two Data Tables of Different Sizes & Comparing for Matching Va...

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Jun 11, 2018 6:13 AM
(3620 views)

Hi,

I have the following problem:

- I have two data tables in seperate jmp files, each of which has one column.

- One table is 8 rows long whilst the other is 6440 rows long.

- My aim is to go through each of the values in the 8-row table and compare them to every value in the 6440-row table to see if they match.

- I was just wondering how I could go about doing this because I want to iterate through both tables but they are different sizes. I was thinking of using a nested for loop but I'm not too sure.

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

I made a slight error in my previous pseudo code example,

`dtShort:Compare Column[i] > dtLong:Compare Column`

Needs to be reversed in logic for it to work

` dtLong:Compare Column > dtShort:Compare Column[i] `

Here is a working example

```
Names Default To Here( 1 );
dtLong = Open( "$SAMPLE_DATA/Big Class.jmp" );
// Create the short data table
dtShort = New Table( "Short",
New Column( "Target Age", values( {12, 16} ) ),
New Column( "Match", character, values( {"Teenager", "Young Adult"} ) )
);
// Sort the data for illustration of data not being
// in age order
dtLong << sort( by( :Name ), replace table( 1 ) );
// Create a couple of new columns in the data table
dtLong << New Column( "Kind Of Match", character );
dtLong << New Column( "Count" );
// Process the data
For( i = 1, i <= N Rows( dtShort ), i++,
// Find all rows that match the Where Clause
theRowsFound = dtLong << get rows where( dtLong:Age > dtShort:Target Age[i] );
// If some rows were found process
If( N Rows( theRowsFound ) > 0,
// Set a value in the dtLong data table with one statement
// that sets values in multiple rows
dtLong:Kind Of Match[theRowsFound] = dtShort:Match[i];
// Now set values row by row for individual rows that were found
theCount = 1;
For( k = 1, k <= N Items( theRowsFound ), K++,
dtLong:Count[theRowsFound[k]] = theCount;
theCount = theCount + 1;
);
);
);
```

Jim

5 REPLIES 5

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Iterating Through two Data Tables of Different Sizes & Comparing for Matching Values

I believe the easiest way to do this would be to let JMP do the work for you. If you Join the tables into a new table, you can have JMP create a new column called Match flag, which will tell you if a match was found or not

Tables==>Join

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Iterating Through two Data Tables of Different Sizes & Comparing for Matching Values

Thanks for your reply.

How would you suggest going about the problem if I wanted to check if the value in the 8-row table is *greater *than the value in the 6440-row table?

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Iterating Through two Data Tables of Different Sizes & Comparing for Matching Values

Here is how I would process it.......

```
// This is pseudo code, but it will give you an idea of the flow
dtShort = Open( "<your short data table>" );
dtLong = Open( "<your long data table>" );
For( i = 1, i <= N Rows( dtShort ), i++,
theRowsFound = dtLong << get rows where( dtShort:Compare Column[i] > dtLong:Compare Column );
If( N Rows( theRowsFound ) > 0,
// Do your processing
);
);
```

Jim

Highlighted
##

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Re: Iterating Through two Data Tables of Different Sizes & Comparing for Matching Values

Thanks for the quick reply.

For the following part:

dtLong : Compare Column

Do you mind explaining how I could access all the values in a column without having to reference to each subscript/index of the column?

Highlighted

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

I made a slight error in my previous pseudo code example,

`dtShort:Compare Column[i] > dtLong:Compare Column`

Needs to be reversed in logic for it to work

` dtLong:Compare Column > dtShort:Compare Column[i] `

Here is a working example

```
Names Default To Here( 1 );
dtLong = Open( "$SAMPLE_DATA/Big Class.jmp" );
// Create the short data table
dtShort = New Table( "Short",
New Column( "Target Age", values( {12, 16} ) ),
New Column( "Match", character, values( {"Teenager", "Young Adult"} ) )
);
// Sort the data for illustration of data not being
// in age order
dtLong << sort( by( :Name ), replace table( 1 ) );
// Create a couple of new columns in the data table
dtLong << New Column( "Kind Of Match", character );
dtLong << New Column( "Count" );
// Process the data
For( i = 1, i <= N Rows( dtShort ), i++,
// Find all rows that match the Where Clause
theRowsFound = dtLong << get rows where( dtLong:Age > dtShort:Target Age[i] );
// If some rows were found process
If( N Rows( theRowsFound ) > 0,
// Set a value in the dtLong data table with one statement
// that sets values in multiple rows
dtLong:Kind Of Match[theRowsFound] = dtShort:Match[i];
// Now set values row by row for individual rows that were found
theCount = 1;
For( k = 1, k <= N Items( theRowsFound ), K++,
dtLong:Count[theRowsFound[k]] = theCount;
theCount = theCount + 1;
);
);
);
```

Jim