Challenge #35: Data Cleansing Practice

[Sreedhar_2798]

1)  Remove leading zeros

For this, I have removed leading zeros using ReplaceChar([Field1], "0", ""),

By this, we are replacing zeros with empty strings.

Here, the point to be noted is, we are lucky with input dataset.

zeros are present only in beginning of every row in "Field1". However, if the zeros are present at different place in the string, we face the risk of the zeros (which are not present at beginning of string) being converted to empty strings. 

In order to address this, we can simply convert the data type of the "Field1" from string data type to integer data type using "SELECT" tool. This will remove ONLY the leading zeros. Please find the solution in attached image 

2)  To remove leading zeros , I have used TrimLeft([Field1],"0") in formula tool 

By this, we are trimming the leading  zeros 

To remove the text at the end , we are just changing the data type to "int32" for 'field1" using "select" tool 

Please find the solution in attached image 

3) To remove ID if it is present at the last, I have used the following in configuration window of formula tool

IF Right([ID],2)="ID" THEN
TrimRight([ID],"ID") ELSE [ID]
ENDIF

Please find the solution in attached image 

4) To achieve the given task, use the following in configuration window of formula tool

IF Length([Safety Code])>8
THEN
Substring([Safety Code],0,8)
ELSEIF Length([Safety Code])=6
THEN
"SC"+[Safety Code]
ELSE
[Safety Code]
ENDIF

Please find the attached image for the solution 

[TheresaLau]

Here is my ans.

[KCParikh]

# 35

[FG1]

My solutions:

[TyroneW]

Solution attached

[hnakabara]

Completed

[lhalliwell]

Spoiler

 

[Mithun_485]

C35_sol

[alisonpitt]

Spoiler

[lizanne_mouton]

Challenge 35