It’s World Statistics Day! To honor the theme of the day, the JMP User Community is having conversations about the importance of trust in statistics and data. And we want to hear from you! Tell us the steps you take to ensure that your data is trustworthy.
Choose Language Hide Translation Bar
Highlighted
Level III

## Populate new column based on values of another column

I'd like to do something like an Excel VLOOKUP  ----   where I'd like to populate an empty new column (New_Status) with values from the original column (STATUS) ---  but some of the values need to be translated....   many of the original status entries do not state explicitly that the item is 'Open' but for my purposes they are open.... so I'd like a column that consolidates all the text that means 'open' to the word 'open...

Original Column:                                                                                       Translate to this new Column:

 STATUS New_Status Closed Closed Pending Group Discussion Open Open Open Open Open Closed ---->   Translate to this column  ---> Closed Rejected Rejected Open Open Open Open Ready to Deploy to QA Open Ready to Deploy to QA Open Ready to Deploy to QA Open QA Testing In Progress (on QA Platform) Open Open Open Resolved Resolved Open Open Ready to Deploy to QA Open Open Open Ready to Deploy to QA Open
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Populate new column based on values of another column

You can create a column formula for this.  You'll have to keep it up to date with valid Open values.  Assuming that dt points to your table:

dt << New Column( "New Status Formula", Character, Nominal,

Formula(If (:STATUS == "Open" |

:STATUS == "Pending Group Discussion" |

:STATUS == "Ready to Deploy to QA" |

:STATUS == "QA Testing In Progress (on QA Platform)",

// then

"Open",

// else

:STATUS

)

)

);

3 REPLIES 3
Highlighted
Super User

## Re: Populate new column based on values of another column

You can create a column formula for this.  You'll have to keep it up to date with valid Open values.  Assuming that dt points to your table:

dt << New Column( "New Status Formula", Character, Nominal,

Formula(If (:STATUS == "Open" |

:STATUS == "Pending Group Discussion" |

:STATUS == "Ready to Deploy to QA" |

:STATUS == "QA Testing In Progress (on QA Platform)",

// then

"Open",

// else

:STATUS

)

)

);

Highlighted
Super User

## Re: Populate new column based on values of another column

An alternative to If() is the Match() function that may be easier to maintain if the non-open statuses are static and few

The formula below returns "Open" for all except for "Closed",  "Rejected" and   "Resolved".

Match( :STATUS,

"Closed", "Closed",

"Rejected", "Rejected",

"Resolved", "Resolved",

"Open"

)

Highlighted
Level III

## Re: Populate new column based on values of another column

PM --- THANK YOU -----     for your quick response and it works perfectly ------

Article Labels

There are no labels assigned to this post.