nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Update column with values when another columns contains specific value

Rini_Kar

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.

Accepted answers

txnelson

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

All comments

Mark_Bailey

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

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

txnelson

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

Rini_Kar

Thanks Jim. It worked perfectly.

Thanks Mark for your script.

Pavithra

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

ACraig

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