Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Re: How can I select a value from an external table by indexing on values in two...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 14, 2015 7:36 AM
(7439 views)

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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",

Add Rows**(****10****)**,

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",

Add Rows**(****5****)**,

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?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

MS

Thanks. Simple solution and works nicely.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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",

Add Rows**(****10****)**,

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",

Add Rows**(****5****)**,

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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",

Add Rows**(** **2400** **)**,

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

**)**;