Keboola WIKI‎ > ‎Transformations‎ > ‎Redshift‎ > ‎

Filling the gaps in currency extractor output in RS

by lucie.sperkova@gmail.com


Description: We’ve realized that ECB is not providing exchange rates during weekends and holidays.

Here you can find UPDATE statement which fills the the gaps with the nearest previous NOT NULL value.


Tables:

  • <dates> - list of dates

  • <rates> - exchange rates


Columns:

  • <datum> - date from <dates> table

  • <date> - date of the exchange rate

  • <fromcurrency> - currency we exchange from

  • <tocurrency> - currency we exchange to

  • <rate> - exchange rate


More explanation of this problem at Filling the gaps in currency extractor output in MySQL

Code:

/* joins the rates table to dates table so we recognize what dates dont have rates*/
CREATE TABLE temp AS
SELECT
dates.date as datum,
rates.*
FROM dates
LEFT JOIN rates
ON rates.date=dates.date
WHERE dates.date BETWEEN '2010-01-04' AND CURRENT_DATE
ORDER BY 1;

/*creates a column with the previous date - the values are relevant only for the null records*/
CREATE TABLE prev_date as
select datum, fromcurrency, tocurrency, rate,
lag(date,1) ignore nulls over (order by datum) as prev_date
from temp;



/* joins not null records from table temp with the filled gaps*/
CREATE TABLE currency AS
(SELECT
datum as date, fromcurrency, tocurrency, rate from temp where rate is not null)
UNION
/* joins previous date to current date and fills the nulls with the records from the previous date*/
(select prev_date.datum as date, pv.fromcurrency, pv.tocurrency, pv.rate 
from prev_date
left join prev_date pv on prev_date.prev_date = pv.datum and pv.rate is not null
where prev_date.rate is null);




Comments