To keep your data clean and organized, it is vital that your values are entered in the same format. Because we are all human, this, of course is not always the case.


Not to worry, the following expert rule has our software clean up the mess for you.

 

EXAMPLE:


Let’s say you have a column in the datafile you wish to import with values of which some are written in uppercase, others in lowercase, for instance different locations like in the image below.



This column may have entries like PARIS and Amsterdam and amsterdam. To clean this up and have the entire column set up in the same format, we need to set up the following expert creation rule in the database.



Assuming you would want to change the values into all uppercase entries you can copy/paste the part between the ***** script ***** markings into your expert rule text area.


The only manual edit you need to perform in the script below is replace the column name “Location" (in black) with the name of your own column.


The first 2 lines of the script are there to declare values.

Line 3 converts the value of the column “TempValueLocation” into a values with uppercase characters.

Line 4 actually changes the values in the column “Location” into uppercase characters.

 


***** script ****

 

var TempValueLocation = DRE.getColumn ("Location");

var String;

 

var String = (TempValueLocation.toUpperCase());

               

DRE.createOrChangeValueInDB("Location", String);

 

***** script ****


 

 You can see the result below:



Please Note: Keep in mind that the expert rule as shown above will overwrite the original values in the column Location. If you wish to keep the original data and enter the clean values as an extra column you could use the following script (the part between the ***** script *****):



***** script ****

 

var TempValueLocation = DRE.getColumn ("Location");

var String;

var Location_New

 

var String = (TempValueLocation.toUpperCase());

               

DRE.createOrChangeValueInDB("Location_new", String);

 

***** script ****



In this case, don't forget to add the column "Location_new" (or a name of your liking) to the definitions of the columns first.