We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!Submit Feedback
The link to the solution for last challenge #34 is HERE. For this challenge let’s practice some data cleansing.
Use Case: There is a series of data cleansing processes we need to do on our data. Please solve each per the instructions.
1. Remove Leading Zeros.
For this, given the codes we are looking for are 4 digit numeric codes, I simply converted to a numeric field. This automatically drops the leading zeros:
2. Isolate 4 digit numeric code.
Here, I simply used Text to Columns to split the columns on ":". I then dropped the superfluous columns:
3. Remove "SD" from the end of any records on which it appears.
This was achieved by simply using the "Trimright()" function to remove any instances of "SD":
4. Return 8 character string and add "US" to the start of strings that are shorter than 8 characters.
I used a simple IF statement to determine the records that were shorter than 8 characters, and if so, add the characters "US" to the beginning. This was coupled with a left() function to return the 8 character string:
5. Return 5 digit code rather than descriptions on given codes.
Here, I used the join tool to join "SBU Code" in the input data stream to the "Description" field in the source table. The left output returns the correct three codes. The output of the join shows those records where the code is in the description field. Rename this output and union back to the input stream to return all records correctly: