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

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

Jaz — Mon, 11 Jun 2018 13:13:25 GMT

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.

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

txnelson — Mon, 11 Jun 2018 13:18:13 GMT

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

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

Jaz — Mon, 11 Jun 2018 13:30:50 GMT

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?

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

txnelson — Mon, 11 Jun 2018 13:52:10 GMT

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
	);
);

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

Jaz — Wed, 13 Jun 2018 09:34:53 GMT

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?

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

txnelson — Wed, 13 Jun 2018 12:35:13 GMT

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;
		);
	);
);