16 Replies Latest reply: Feb 26, 2016 12:01 AM by John Jackson RSS

    How to create Calculated field based on excel file

    John Jackson
      String TaskID
      The oranges in the trees were growing1001
      mint, herb, basil are my kinda thing1002
      dogs, cats and parrots make great pets1003

       

       

      I have the 2 above fields Spring Task and ID, I want to achieve the following:

      if the field contains text *orange*, I want a value Fruit under a new calculated field , Result.

      similar, I want Results, plant for *herb*, Animal for *dog* etc..

       

      I know I can do this by if(wildmatch([String Task],'*orange*'>0, 'Fruit') as Result

      Instead I want to maintain an excel file where I can store values for search and result

      as below. so that the parameters in wildmatch can be substituted by below table. how can I do this. May be a lookup table or minstring.

      any help will be greatly appreciated/ Much ThanksHow

       

      Field AField B
      OrangeFruit
      mintplant
      doganimal