cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Jaz
Jaz
Level IV

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
txnelson
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

View solution in original post

5 REPLIES 5
txnelson
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
Jaz
Jaz
Level IV

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?

txnelson
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, 
		// Do your processing
	);
);
Jim
Jaz
Jaz
Level IV

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? 

txnelson
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