Data Cleaning

Hi guys!

I am trying to clean bunch of time codes and I have a first line code that's valid, but I am having trouble cleaning other codes such as 'hourly' and 'salary' lines of code without error.

case when Right(`Entry Store`,5) = 'PTO"}' then 'PTO' end 

Best Answers

  • GrantSmith
    GrantSmith Indiana 🥷
    Answer ✓

    How are you attempting to clean these codes? What is the business logic you’re needing to replicate?

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷
    Answer ✓

    When you have multiple items to clean, you will have one case statement and multiple when statements, like this:


     case when Right(`Entry Store`,5) = 'PTO"}' then 'PTO'
     when Right(`Entry Store`,5) = 'VAC"}' then 'VAC'
     when Right(`Entry Store`,5) = 'SIC"}' then 'SICK'
     when Right(`Entry Store`,3) = 'OTH' then 'OTHER'
      end 
    

    you can vary what you are evaluating on as well. In the last when statement, for example, I am just looking at the last 3 character of the field. I could also look at a totally different field if I wanted. You can also add an ELSE statement just before end to deal with any scenarios you didn't account for, like this

     case when Right(`Entry Store`,5) = 'PTO"}' then 'PTO'
     when Right(`Entry Store`,5) = 'VAC"}' then 'VAC'
     when Right(`Entry Store`,5) = 'SIC"}' then 'SICK'
     when Right(`Entry Store`,3) = 'OTH' then 'OTHER'
     else `Entry Store` end 
    


    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.

Answers

  • Fadem
    Fadem ⚪️

    I am extracting the words PTO from a long line of code. with the below beast mode:

    case when Right(`Entry Store`,5) = 'PTO"}' then 'PTO' end 

    that cleans the PTO code however, when I try using the same beast mode approach as above and add more lines for other codes such as hourly I am getting a syntax error.

  • Fadem
    Fadem ⚪️

    @MarkSnodgrass Thank you so much! That was helpful.