Subscribe Bookmark RSS Feed

Update column with values when another columns contains specific value

Rini_Kar

Occasional Contributor

Joined:

Sep 10, 2017

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

Joined:

Jun 22, 2012

Solution

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
4 REPLIES
markbailey

Staff

Joined:

Jun 23, 2011

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

Occasional Contributor

Joined:

Sep 10, 2017

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

Joined:

Jun 22, 2012

Solution

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

Occasional Contributor

Joined:

Sep 10, 2017

Thanks Jim. It worked perfectly.

Thanks Mark for your script.