topic Re: Populate new column based on values of another column in Discussions

Populate new column based on values of another column

twaintwist — Fri, 15 Jun 2012 19:34:50 GMT

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

Re: Populate new column based on values of another column

pmroz — Fri, 15 Jun 2012 19:46:06 GMT

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

)

);

Re: Populate new column based on values of another column

ms — Fri, 15 Jun 2012 19:55:33 GMT

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"

)

Re: Populate new column based on values of another column

twaintwist — Fri, 15 Jun 2012 20:12:11 GMT

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