Data Cleaning

Fadem
Fadem Member
in Charting

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 Coach
    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 Coach
    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

  • GrantSmith
    GrantSmith Coach
    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!**
  • Fadem
    Fadem Member

    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.

  • MarkSnodgrass
    MarkSnodgrass Coach
    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.
  • Fadem
    Fadem Member

    @MarkSnodgrass Thank you so much! That was helpful.

Categories