59911_en-US

liliconcom

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.

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</CODE></PRE>
<P><SPAN>Needs to be reversed in logic for it to work</SPAN></P>
<PRE><CODE class=" language-jsl"> 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;
  );
 );
);

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? 

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="https://community.jmp.com/" table="">" );
dtLong = Open( "<YOUR long="" data="https://community.jmp.com/" 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
 );
);</YOUR></YOUR>

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?

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