Forum Discussion

jcollier's avatar
jcollier
Frequent Visitor
8 years ago
Solved

New column based on other's value

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:

 

employeestatus
Johnidle
William 7861
Annatraining
Francescleaning
Jason

#foo

 

Table with categories:

 

statuscategory
idleinactive
7861inactive
trainingactive
cleaningactive
#fooinactive
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

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

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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's avatar
      jcollier
      Frequent Visitor
      8 years ago

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