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); |
There are few things which I am sure, you would know if you have used EDQ before.
- 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.
- There can be only one output of the script and you need to store it in output1 variable only.
- 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 🙂
Analects of an Otaku 