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

[#1]

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

[#2]

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.

[#3]

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.

[#4]

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.

[#5]

MS

Thanks. Simple solution and works nicely.

[#6 · Accepted solution]

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

[#7]

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 )

);