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
ranjan_mitre_or
Level III

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
ms
Super User (Alumni) ms
Super User (Alumni)

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

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

View solution in original post

6 REPLIES 6

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
ranjan_mitre_or
Level III

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.

ms
Super User (Alumni) ms
Super User (Alumni)

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.

ranjan_mitre_or
Level III

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.

ms
Super User (Alumni) ms
Super User (Alumni)

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

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

pmroz
Super User

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

    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 )

);