Update column with values when another columns contains specific value

[#1]

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.

 

[#2]

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.

[#3]

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 scriptAfter script

[#4 · Accepted 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);
);

[#5]

Thanks Jim. It worked perfectly.

Thanks Mark for your script.

[#6]

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

---

[#7]

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? 

[#8]

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




);

[#9]

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?

[#10]

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