Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted

## How can I select a value from an external table by indexing on values in two columns in the JMP table?

I am working in a very large JMP table. I would like to select a value from an external two dimensional small table and put that value in a new column. In order to select the value from the external table, I would like to use values in each row of the two columns in the current JMP table as the two subscripts of the external small table. Can anyone help me to do this?

I have  two files. The Big_data_matrix contains the first column that can only have values between 1 - 24. Column 2 is empty. Values.jmp is a small table that contains one column with 24 entries. For each row in Big_data_matrix I would like to read the value in column 1, and if it is equal to k, then I would like to pick the k-th entry (k=1:24) from values. jmp table, and enter it in the second column of Big_data_matrix.

My original Big_data_matrix is very large (>1M rows) and I have to do this for many different Big_data_matrix data tables.

This is probably easy to do in jsl, but I have 0  experience with JSL, and would like to do this within JMP, if at all possible.

Thanks.

Message was edited by: Ranjan Sonalkar

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

## Re: How can I select a value from an external table by indexing on values in two columns in the JMP table?

Here is an example on how to use jsl to get values from another tables based on 2D-coordinates in the main table.

//Example tables

dt1 = New Table("BigTable",

New Column("row_index", Set Values([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])),

New Column("column_index", Set Values([1, 2, 3, 1, 2, 3, 1, 2, 3, 1]))

);

dt2 = New Table("SmallTable",

New Column("Column 1", Set Values([1, 2, 3, 4, 5])),

New Column("Column 2", Set Values([6, 7, 8, 9, 10])),

New Column("Column 3", Set Values([11, 12, 13, 14, 15]))

);

// Add new columns values from small tables corresponding to the two index columns

dt1 << New Column("data", numeric);

For Each Row(dt1:data = Column(dt2, Eval(:column_index[]))[:row_index]);

6 REPLIES 6
Highlighted

## Re: How can I select a value from an external table by indexing on values in two columns in the JMP table?

Hello,

Could you provide a small example of your tables and the desired output?  That should help to clarify what you are trying to accomplish.

Thanks.

Wendy
Highlighted

## Re: How can I select a value from an external table by indexing on values in two columns in the JMP table?

I have  two files. The Big_data_matrix contains the first column that can only have values between 1 - 24. Column 2 is empty. Values.jmp is a small table that contains one column with 24 entries. For each row in Big_data_matrix I would like to read the value in column 1, and if it is equal to k, then I would like to pick the k-th entry (k=1:24) from values. jmp table, and enter it in the second column of Big_data_matrix.

My original Big_data_matrix is very large (>1M rows) and I have to do this for many different Big_data_matrix data tables.

This is probably easy to do in jsl, but I have 0  experience with JSL, and would like to do this within JMP, if at all possible.

Thanks.

Highlighted

## Re: How can I select a value from an external table by indexing on values in two columns in the JMP table?

Ok, the indexing is one-dimensionalI. Then I suggest that you use Update.

First, add a second column to Values.jmp, with the values 1 to 24. Then, activate the big table and select Update in the tables menu. Select Values.jmp in the list box and check "Match columns". Then you match the first column in big table with the newly created ID column in Values.jmp.

A new column with values corresponding to the number in column 1 is then added to the big table (if the empty column 2 in big table are given the same name as the value column in the small table, this column will be filled instead of a new column).

And yes, It could easily be scripted.

Highlighted

## Re: How can I select a value from an external table by indexing on values in two columns in the JMP table?

MS

Thanks. Simple solution and works nicely.

Highlighted

## Re: How can I select a value from an external table by indexing on values in two columns in the JMP table?

Here is an example on how to use jsl to get values from another tables based on 2D-coordinates in the main table.

//Example tables

dt1 = New Table("BigTable",

New Column("row_index", Set Values([1, 2, 3, 4, 5, 1, 2, 3, 4, 5])),

New Column("column_index", Set Values([1, 2, 3, 1, 2, 3, 1, 2, 3, 1]))

);

dt2 = New Table("SmallTable",

New Column("Column 1", Set Values([1, 2, 3, 4, 5])),

New Column("Column 2", Set Values([6, 7, 8, 9, 10])),

New Column("Column 3", Set Values([11, 12, 13, 14, 15]))

);

// Add new columns values from small tables corresponding to the two index columns

dt1 << New Column("data", numeric);

For Each Row(dt1:data = Column(dt2, Eval(:column_index[]))[:row_index]);

Highlighted

## Re: How can I select a value from an external table by indexing on values in two columns in the JMP table?

You can use the UPDATE statement to update the large table with values from the small table.  Here's a JSL example:

dt_big = New Table( "Big_data_matrix",

New Column( "First Column", Numeric, Continuous,  Format( "Best", 12 ) )

);

dt_val = New Table( "Values", Add Rows( 24 ),

New Column( "Column 1", Character, Nominal,

Set Values( {"AA", "BB", "CC", "DD", "EE", "FF", "GG", "HH", "II", "JJ", "KK", "LL",

"MM", "NN", "OO", "PP", "QQ", "RR", "SS", "TT", "UU", "VV", "WW", "XX"} ) ),

New Column( "Row Number", Numeric, Continuous, Format( "Best", 12 ),

Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20,

21, 22, 23, 24] ) )

);

// Fill with random integers from 1-24

for (i = 1, i <= 2400, i++,

column(dt_big, "First Column")[i] = round(random uniform(1, 24), 0);

);

dt_big << Update(

With( dt_val ),

Match Columns( :First Column = :Row Number ),

Add Columns from Update table( :Column 1 )

);

Article Labels

There are no labels assigned to this post.