So I started with a bit of work on EDQ and I must say its a pretty neat tool. Basically Oracle EDQ stands for Enterprise Data Quality where you get to fix all the invalid, inconsistent and simply put bad, very bad, very very bad data.

I was just playing with it a bit and found the script processor very handy since I could do anything I want with use of a scripting language like javascript or groovy.

I just wrote a sample script to have convert the currency values of products from one currency to another. Maybe this could help someone writing a script for the first time in EDQ.

Here is what my data looks like –

+--------+--------------+------------+-----------------+
| Value | Convert From | Convert To | Converted Value |
+--------+--------------+------------+-----------------+
| 100.14 | INR | USD | |
| 290.19 | INR | GBP | |
| 310.05 | INR | EUR | |
| 110.67 | INR | CAD | |
| 200 | EUR | EUR | |
| 10 | EUR | GBP | |
| 3 | EUR | INR | |
| 6 | GBP | INR | |
| 6 | EUR | INR | |
+--------+--------------+------------+-----------------+

I created a quick web service on mockable.io. It is a pretty neat tool where you can create a web service to output any data that you want to, here is the sample data I created –

{"base":"EUR","rates":{"CAD":1.4469,"HKD":8.4742,"ISK":139.3,"PHP":55.538,"DKK":7.471,"HUF":339.28,"CZK":25.344,"AUD":1.6565,"RON":4.805,"SEK":10.5813,"IDR":15221.86,"INR":77.9195,"BRL":4.7741,"RUB":71.2368,"HRK":7.4605,"JPY":120.13,"THB":34.664,"CHF":1.0606,"SGD":1.5204,"PLN":4.3094,"BGN":1.9558,"TRY":6.6981,"CNY":7.6329,"NOK":10.2113,"NZD":1.7273,"ZAR":16.4555,"USD":1.0875,"MXN":20.806,"ILS":3.7429,"GBP":0.8415,"KRW":1322.29,"MYR":4.5952}}

And finally here is the code of the script processor which will convert the values to specific currency, the data used is pretty self explanatory.

addLibrary("http");
var output1;
var xmlHttp = new XMLHttpRequest();
xmlHttp.open("GET", "http://demo7684243.mockable.io/curr_rates", false);// Here goes your web service URL
xmlHttp.send();
var responseData = xmlHttp.responseText;
var dataJSON = JSON.parse(responseData);
var baseCurr = dataJSON["base"];
var baseCurrRate = dataJSON["rates"];
var convertFrom = input1[1];
var convertTo = input1[2];
var convertFromRate = dataJSON["rates"][convertFrom];
var convertToRate = dataJSON["rates"][convertTo];
if (convertFrom == convertTo) {
convertToRate = 1;
convertFromRate = 1;
}
if (convertTo == baseCurr) {
convertToRate = 1;
}
if (convertFrom == baseCurr) {
convertFromRate = 1;
}
var convertedVal = (input1[0] * convertToRate) / convertFromRate;
output1 = convertedVal.toFixed(2);
view raw edq_script_proc.js hosted with ❤ by GitHub

There are few things which I am sure, you would know if you have used EDQ before.

  1. All inputs and outputs have to be used in the form of array. input1[0], input1[1], input1[2] and so on will be automatically mapped with your columns which you would have selected.
  2. There can be only one output of the script and you need to store it in output1 variable only.
  3. You can simply ignore the logic of Currency conversion in the code, since this is for a very specific task.

All this was just to show you how you can manipulate data with the use of Web Services in EDQ. Hope this helps šŸ™‚