New column based on other's value

[jcollier]

I have a table with two columns: employees and their status.

What I'd like to do:

I'd like to classify the status into two categories: active and inactive.

I know I should create new table only with status and its assigned category, then make a relationship.

Questions:

1. How can I automatically classify status starting with number as 'inactive' category?
2. How can I automatically classify status with text 'idle' as 'inactive' category?

Original table:

employee	status
John	idle
William	7861
Anna	training
Frances	cleaning
Jason	#foo

Table with categories:

status	category
idle	inactive
7861	inactive
training	active
cleaning	active
#foo	inactive

[Greg_Deckler · Accepted solution]

This catches everything except #foo, not sure of the parameters around #foo. Is it if it begins with a hashtag?

Column = IF(NOT(ISERROR(VALUE(LEFT([status],1)))) || [status] = "idle", "inactive", "active")

[jcollier]

Thanks to you I also came up with solution that let's you calculate column based on first X letters e.g if value starts with "tr" it should be classified as "active".

Column = 
SWITCH(
 TRUE();
 IF(NOT(ISERROR(VALUE(LEFT('status'[status];1))));TRUE();FALSE());"active";
 SEARCH("idle";'status'[status];;0)>0;"inactive";
 SEARCH("tr";LEFT('status'[status];2);;0)>0;"active";
 "other-category")

[Greg_Deckler]

Awesome! Glad I could be of assistance!