topic Update column with values when another columns contains specific value in Discussions

Update column with values when another columns contains specific value

Rini_Kar — Tue, 03 Oct 2017 16:30:44 GMT

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.

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

Mark_Bailey — Tue, 03 Oct 2017 15:16:18 GMT

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.

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

Rini_Kar — Tue, 03 Oct 2017 16:31:57 GMT

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

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

txnelson — Tue, 03 Oct 2017 16:58:56 GMT

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

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

Rini_Kar — Tue, 03 Oct 2017 17:06:51 GMT

Thanks Jim. It worked perfectly.

Thanks Mark for your script.

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

Pavithra — Fri, 18 Jan 2019 09:38:51 GMT

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

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

theseventhhill — Thu, 18 Jul 2019 19:19:59 GMT

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?

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

txnelson — Fri, 19 Jul 2019 01:53:30 GMT

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




);

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

theseventhhill — Fri, 19 Jul 2019 02:08:03 GMT

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?

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

txnelson — Fri, 19 Jul 2019 02:16:39 GMT

Do not convert the date value to character.
In numeric columns(which a date column is) the "." indicates the value is misssing.
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

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

ACraig — Mon, 18 May 2020 22:03:12 GMT

Hello,

This might be a silly question but when using this code do you use it by editing the formula of the column? I did it as a formula like this since I am not doing it based off of a missing value. I am trying to update my column Vendor so that when the column Label equals "MnDOT Est." the vendor equals "MnDOT". But it gives me a Column Vendor Formula Interrupted error with an Illegal Reference thing. It changes the ones to MnDOT but it just deletes all the others. I hope that makes sense.

For Each Row(
        temp = :Vendor;
        :Vendor = If( :Label == "MnDOT Est.", "MnDOT", temp);
);

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

txnelson — Mon, 18 May 2020 22:24:22 GMT

This code needs to be run as a separate script, not as a formula.