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.
Jackie_
Level VI

Search the string in Column and copy the numeric value in 2nd column

I have an Excel Worksheet consisting of two columns, one columns has strings with person name and other column contain age of the person. I have almost a list of 500 person. Is there any way I can automate it?Like using script to assign age based on the person name ?

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Re: Search the string in Column and copy the numeric value in 2nd column

Here is a functioning example to learn from:

 

Names Default to Here( 1 );

// open reference data table
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

// make container for lookup
lookup  = Associative Array();

// get names and ages for lookup
For Each Row(
	Insert Into( lookup, dt:name, dt:age );
);

Close( dt, No Save );

// open new table (use Big Class again, delete existing age column)
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Delete Columns( :age );

// make new age column and populate it using lookup table
dt << New Column( "new age", "Numeric", "Continuous" );
For Each Row(
	:new age  = lookup[ :name ];
);

View solution in original post

11 REPLIES 11
txnelson
Super User

Re: Search the string in Column and copy the numeric value in 2nd column

Assuming the list of 500 names is in a JMP data table, and you have that csv file with the Names and Ages, you can easily input the csv file, and then use

     Tables==>Join

to match the name/age data table to the table with the list of 500 names.

Jim
Jackie_
Level VI

Re: Search the string in Column and copy the numeric value in 2nd column

Yes that's the easiest approach, but the problem is JMP data file has 10000 data sets of different names and the excel sheet has random 500 names from 10000 data sets of jmp file. So I want something which can search those particular name in the excel and past the age from jmp data file 

txnelson
Super User

Re: Search the string in Column and copy the numeric value in 2nd column

You have 10,000 JMP data tables....correct?  What are the data table names?  Can that be used to determine how to find the data table that has the age for a given name in the Excel spreadsheet?

 

Can you provide information about what data are in the 10,000 JMP data tables, and how do you get the age from the data table, once you find the correct JMP data table?

 

More specifics are really needed before one can attempt to solve your problem.

Jim
Jackie_
Level VI

Re: Search the string in Column and copy the numeric value in 2nd column

Hi Txnelson,

 

The JMP file has 10,000 rows and 2 columns. The 1st column name is Person and the 2nd is age. Yes it can be used to find the data table that has the age for a given name in the Excel spreadsheet.

 

The JMP data file has 10,000 rows having different names and ages (Strings and numbers)

 

Both the person name and age are listed in the JMP data file.

 

I have a excel file which has two columns and 500 rows, the 1st column has the persons name and the 2nd contains ages. I am trying to write a script which will look for all 500 data (person name) from excel from in the JMP data file and copy the age value corresponding to the person's name in the excel file.

 

 

Re: Search the string in Column and copy the numeric value in 2nd column

This case would be a good time to import the Excel workbook with the names and ages and then load them into an associative array for lookup.

Jackie_
Level VI

Re: Search the string in Column and copy the numeric value in 2nd column

Hi mark,

Thanks for the suggestions. I am new to the JMP could you please suggest how I can start ?

txnelson
Super User

Re: Search the string in Column and copy the numeric value in 2nd column

While @Mark_Bailey suggestion of using an Associative Array, will work, I believe that a simple Join will probably solve the problem.

  1. Open the JMP table with 10,000 rows
  2. Open in JMP, the Excel file
  3. Using the JMP table from Excel as the base table, use
    1. Tables==>Update      to match the names and add in the ages to the base table
  4. Save the updated table back as an Excel file

I suggest you take the time to read the documentation  "Discovering JMP" and "Using JMP"

     Help==>JMP Documentation Library

Jim

Re: Search the string in Column and copy the numeric value in 2nd column

Here is a functioning example to learn from:

 

Names Default to Here( 1 );

// open reference data table
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

// make container for lookup
lookup  = Associative Array();

// get names and ages for lookup
For Each Row(
	Insert Into( lookup, dt:name, dt:age );
);

Close( dt, No Save );

// open new table (use Big Class again, delete existing age column)
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt << Delete Columns( :age );

// make new age column and populate it using lookup table
dt << New Column( "new age", "Numeric", "Continuous" );
For Each Row(
	:new age  = lookup[ :name ];
);
KimRas2
Level I

Re: Search the string in Column and copy the numeric value in 2nd column

Hi Mark,

I have a similar task, but my "key" is a continuous data type.

In Excel it is very easy to create a look-up table and use it to transform highly un-linear characteristics.

In this case it is battery voltage data to estimates of state-of-charge (SoC) and vice versa. So, I would like to convert e.g. a data table with measured voltage data to SoC estimates. One challenge is that my look-up table may/will not have all the many voltage levels existing in the data, so I need the SoC corresponding to the nearest voltage level. E.g. voltage data is 3.801 V and look-up table has 3.800 V as the nearest and a corresponding SoC of 51%.

Sometimes my task is to convert "backwards" (SoC => voltage), but hopefully I could create that solution on my own given good inspiration ;)

 

BR, Kim.