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 the software clean up the mess for you.

 

EXAMPLE:


Let’s say you have a column in the datafile you wish to import with numeric values, containing decimals, for instance a purchase amount like the image below.



This column may have entries like €1257,85 and €758,55 and €237,00, but also €5915.45 and €115.75. 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 dot into a comma 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 “Purchase amount" (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 “TempValueAmount” into a value with a comma as a decimal sign.

Line 4 actually changes the dot in the column “Purchase amount” into a comma.

 


***** script ****

 

var TempValueAmount = DRE.getColumn ("Purchase amount");

var String;

 

var String = (TempValueAmount.replace(".", ","));

               

DRE.createOrChangeValueInDB("Purchase amount", String);

 

***** script ****

 

 You can see the result below: