cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
twaintwist
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: 

                                              

STATUSNew_Status
ClosedClosed
Pending Group DiscussionOpen
OpenOpen
OpenOpen
Closed   ---->   Translate to this column  --->Closed
RejectedRejected
OpenOpen
OpenOpen
Ready to Deploy to QAOpen
Ready to Deploy to QAOpen
Ready to Deploy to QAOpen
QA Testing In Progress (on QA Platform)Open
OpenOpen
ResolvedResolved
OpenOpen
Ready to Deploy to QAOpen
OpenOpen
Ready to Deploy to QAOpen
1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
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

            )

    )

);

View solution in original post

3 REPLIES 3
pmroz
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

            )

    )

);

ms
Super User (Alumni) ms
Super User (Alumni)

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"

)

twaintwist
Level III

Re: Populate new column based on values of another column

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