Data Cleaning

Fadem · 2022-08-09T20:43:49+00:00

There was an error rendering this rich post.

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

Find more posts tagged with

Accepted answers

GrantSmith

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

MarkSnodgrass

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

All comments

GrantSmith

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

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.

MarkSnodgrass

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

 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

Fadem

@MarkSnodgrass Thank you so much! That was helpful.