BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Rini_Kar
Community Trekker

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

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
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
9 REPLIES 9

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.

 

Learn it once, use it forever!
Rini_Kar
Community Trekker

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_script.JPGBefore scriptAfter_script.JPGAfter script

0 Kudos
Highlighted
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
Community Trekker

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

Thanks Jim. It worked perfectly.

Thanks Mark for your script.

0 Kudos
Pavithra
Community Member

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

 

0 Kudos
theseventhhill
Occasional Contributor

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? 

0 Kudos
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
0 Kudos
theseventhhill
Occasional Contributor

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?

0 Kudos
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
0 Kudos