Choose Language Hide Translation Bar
Community Trekker

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

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
Super User

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

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
Super User

## 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
Community Trekker

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

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
Super User

## 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,
);
);
Jim
Highlighted
Community Trekker

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

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
Super User

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

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