10 Replies Latest reply: Jul 11, 2011 4:50 PM by Nagaian Krishnamoorthy RSS

    Scrubbing/Cleaning Data when Load

      Hey All-

       

      I've been scouring the QV discussions and a common theme came up: probably shouldn't use QV for data scrubbing purposes.

       

      I get this, but unfortunately I'm stuck in a situation where QV is the only means (for right now) of accomplishing this task.  That being said, I have one field called CodeName that needs to be scrubbed and cleaned on a weekly basis as it's being loaded into QV.  I'd really rather not create a matchtable to do this because the CodeNames are constantly being created (hundreds on a daily basis), so a MatchTable isn't scalable.

       

      Here are the common factors that need to be scrubbed:

       

      CodeName

      (front)code_123 --> changed to: code_123

      (mainstore)  --> changed to: null

      code_345(station) --> changed to: code_345

       

      Also...  trim functions...  I'd like to cover any instance of a leading or trailing space?  Do I need to do both (e.g. trim( ' abc') and trim('abc ')?  or can I just encompasses everyting under one: trim(' abc ')? 

       

       

      Thanks!