Skip to main content

Workflow #1 — Currency Conversion

This workflow fetches live currency exchange rates from an external API, transforms the data into a structured table, and calculates revenue in local currency (NIS).

Nodes used: Source Script (File) → Data Prep → Query (In-Memory) → Data Prep

Step 1 — Source Script (File)​

Use a Source Script (File) node to call an external API and return the response as a file.

// Fetch exchange rates from the Bank of Israel API
var request = HttpUtil.get("https://boi.org.il/PublicApi/GetExchangeRates");

// Parse the JSON response
var jsonContent = JsonUtil.parse(request.asString().getBody());

// Extract the exchangeRates array and write it to a temp file
var exchanges = JsonUtil.stringify(jsonContent.exchangeRates);
var tempFile = FileUtil.createTempFile("tmp.json", exchanges);

Step 2 — Data Prep (Reformat Dates)​

Connect a Data Prep node to parse the JSON file into a table. Add a custom column to reformat the raw ISO-8601 timestamp into a readable LocalDate:

var instant = Instant.parse(datetime);
var year = InstantUtil.getYear(instant);
var month = InstantUtil.getMonth(instant);
var day = InstantUtil.getDayOfMonth(instant);
LocalDate.of(year, month, day);

Step 3 — Query (In-Memory)​

Use a Query node to join and filter the table, extracting the rates you need:

SELECT current_date AS date,
       t1.rate      AS dollar,
       t2.rate      AS pound,
       t3.rate      AS euro
FROM   DataPrep_5 t1, DataPrep_5 t2, DataPrep_5 t3
WHERE  t1.date = t2.date
  AND  t1.date = t3.date
  AND  t1.CURRENCYCODE = 'USD'
  AND  t2.CURRENCYCODE = 'GBP'
  AND  t3.CURRENCYCODE = 'EUR'

Step 4 — Data Prep (Calculate NIS Amount)​

After joining with a company revenue table, add a custom column that converts each amount to NIS based on its currency:

if (tccur == "USD") { return tamta * dollar; }
else if (tccur == "EUR") { return tamta * euro; }
else if (tccur == "GBP") { return tamta * pound; }

Summary​

This workflow demonstrates how scripting nodes work together to fetch external data, reshape it with Data Prep expressions, query it with SQL, and calculate derived values — all within a single workflow.