I have a data table with a date and I have a currency exchange table with exchange rates for one date per month. I want to match the data table with the exchange rate of the respective month.
I try to use IntervalMatch but I have no range only the beginning of the range. So I used to have something which maches with the nearest date before or same time as the date from my data table.
Or I havt to add end values to my currency exchange table but I dont know how to do the by code.
Has someone any ideas?
Hi Holly, As i understand correctly, you have one transnational table having Date. Other hand, you have exchange rate table having only one row per each month.
Simply Join on the these tables using MonthStart function on both fields.
LOAD *, MonthStart(Date) AS ER_KEY
Load Exchnage_Rate , MonthStart(Date) AS ER_KEY
So All the transactions under one month correctly tied to Exchange_Rate table.
this is a good solution if there is only one exchange entry per month. Unfortunately I have overseen that there are somtimes more than one. So I have to tie to the last date before the fact date.
Hi, you can use Peek to create from and to dates in your exchange table, which will enable you to use intervalmatch
RowNo() AS Row,
If(RowNo() = 1, Null(),
If(peek('Date', -1, 'ExchFromTo') >= Date,
peek('Date', -1, 'ExchFromTo'), Date )) AS FromDate
ORDER BY Currency, Date;
DROP Table ExchangeRates;
one solution could be:
LOAD * Inline [
date start, exchange rate
LOAD Date(Date#('01/01/2014','MM/DD/YYYY')+IterNo()-1) as date,
Money(Ceil(Rand()*1000)) as amount
While Date#('01/01/2014','MM/DD/YYYY')+IterNo()-1 <= Today();
Left Join (tabExchangeRates)
AutoNumberHash128([date start], [date end]) as %PeriodID;
LOAD [date start],
Alt(Date(Peek([date start])-1), Date(Today())) as [date end]
Order By [date start] desc;
Left Join (tabData)
LOAD [date start], [date end]
Left Join (tabData)
AutoNumberHash128([date start], [date end]) as %PeriodID
DROP Fields [date start], [date end] From tabData;
hope this helps