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.
Rini_Kar
Level II

Update column with values when another columns contains specific value

Hi, 

 

I have two columns named 'Mailing country' and 'Mailing state'. Some of the rows under Mailing country particularly for USA does not contain any value.  I would like to update Mailing country column with value 'USA' when Mailing state contains values corresponding to various state code in the US. I have close to 250,000 rows, so manually update is not possible. I am new to JMP and scripting and I cannot figure out a way to solve this issue.

 

I have attached a screenshot of my sample data. Any help would be appreciated. Thanks in advance.

 JMP.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Update column with values when another columns contains specific value

Here is one solution taking a little liberty with Mark's script

For Each Row(
     temp=:Mailing Country;
	:Mailing Country = If( Is Missing( :Mailing Country ) & Contains( {"AL","AZ"}, :Mailing State ), "USA" ,temp);
);
Jim

View solution in original post

11 REPLIES 11

Re: Update column with values when another columns contains specific value

You could use a formula in a new column like this:

If( Is Missing( :Mailing Country ) & Contains( {"AL","AZ"}, :Mailing State ), "USA" )

If you don't want a new column, then you could run this script to replace the missing values in the existing column:

 

For Each Row(
	:Mailing Country = If( Is Missing( :Mailing Country ) & Contains( {"AL","AZ"}, :Mailing State ), "USA" );
);

You would have to expand the list in either case to include the two-letter code for every state before using.

 

Rini_Kar
Level II

Re: Update column with values when another columns contains specific value

Hi Mark,

 

Thank you for your solution.

However, I notice that when I run your script, the rows without the value gets updated with "USA" whereas the rows with value present before, disappears after running the script.

 

I have attached a screenshot of both before and after running the script. Thanks.

 

Before scriptBefore scriptAfter scriptAfter script

txnelson
Super User

Re: Update column with values when another columns contains specific value

Here is one solution taking a little liberty with Mark's script

For Each Row(
     temp=:Mailing Country;
	:Mailing Country = If( Is Missing( :Mailing Country ) & Contains( {"AL","AZ"}, :Mailing State ), "USA" ,temp);
);
Jim
Rini_Kar
Level II

Re: Update column with values when another columns contains specific value

Thanks Jim. It worked perfectly.

Thanks Mark for your script.

Pavithra
Level I

Re: Update column with values when another columns contains specific value

Really helpful.! It worked for my usecase too.! Thanks a lot.!


@txnelson wrote:

Here is one solution taking a little liberty with Mark's script

For Each Row(
     temp=:Mailing Country;
	:Mailing Country = If( Is Missing( :Mailing Country ) & Contains( {"AL","AZ"}, :Mailing State ), "USA" ,temp);
);

 

theseventhhill
Level II

Re: Update column with values when another columns contains specific value

I have a simlar iussue. I have IDs and dates that should be identifical but in different columns because of missing values.

How can i apply this to replace only rows with missing values for 6 columns with values from different set of 6 columns? 

txnelson
Super User

Re: Update column with values when another columns contains specific value

So, if the value in column A is missing, you want the value in column A2 to assigned to column A?   And the same for 5 other columns?

If that is what you are asking, here is how you would do it for column A, and you would repeat for the other 5 columns

For Each Row(
     If( IsMissing(:A), :A = :A2);
     If( IsMissing(:B), :B = :B2);




);
Jim
theseventhhill
Level II

Re: Update column with values when another columns contains specific value

Thanks txnelson.

On a related note, if it is a date entry that is missing, am I bettetr of converting the column to character and then running the script and also, does the "." dot that shows up in missing columns interpreted as missing in the Ismissing() or should it be just blank?

txnelson
Super User

Re: Update column with values when another columns contains specific value

  1. Do not convert the date value to character.
  2. In numeric columns(which a date column is) the "." indicates the value is misssing.
  3. You need to do some reading.  The Books "Using JMP" and "Scripting Guide" are a must read.  You can find them under the Help pull down menu

     Help==>Books==>Using JMP

     Help==>Books==>Scripting Guide

Jim