Data Cleaning

Fadem

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 

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 

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 

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 

Fadem

@MarkSnodgrass Thank you so much! That was helpful.