Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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?
hyharp
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.
Transactions:
LOAD *, MonthStart(Date) AS ER_KEY
From Transactions;
Exchange_Rate:
Load Exchnage_Rate , MonthStart(Date) AS ER_KEY
From ExchangeRate;
So All the transactions under one month correctly tied to Exchange_Rate table.
Hi Dathu,
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.
Please Share the some sample data so I will give some script solution for this.
Hi, you can use Peek to create from and to dates in your exchange table, which will enable you to use intervalmatch
ExchFromTo:
LOAD
*,
RowNo() AS Row,
If(RowNo() = 1, Null(),
If(peek('Date', -1, 'ExchFromTo') >= Date,
peek('Date', -1, 'ExchFromTo'), Date )) AS FromDate
RESIDENT ExchangeRates
ORDER BY Currency, Date;
DROP Table ExchangeRates;
Regards,
M
It's a one-liner with Qlikview Components http://qlikviewcomponents.org
CALL Qvc.ExpandInterval ('RateTable', 'RateDate')
-Rob
Hi hyharp,
one solution could be:
tabExchangeRates:
LOAD * Inline [
date start, exchange rate
01/01/2014, 1.513
02/01/2014, 1.456
03/01/2014, 1.417
03/15/2014, 1.348
04/01/2014, 1.389
04/10/2014, 1.391
04/20/2014, 1.498
05/01/2014, 1.568
06/01/2014, 1.591
06/25/2014, 1.643
07/01/2014, 1.719
08/01/2014, 1.737
];
tabData:
LOAD Date(Date#('01/01/2014','MM/DD/YYYY')+IterNo()-1) as date,
Money(Ceil(Rand()*1000)) as amount
AutoGenerate 1
While Date#('01/01/2014','MM/DD/YYYY')+IterNo()-1 <= Today();
Left Join (tabExchangeRates)
LOAD *,
AutoNumberHash128([date start], [date end]) as %PeriodID;
LOAD [date start],
Alt(Date(Peek([date start])-1), Date(Today())) as [date end]
Resident tabExchangeRates
Order By [date start] desc;
Left Join (tabData)
IntervalMatch (date)
LOAD [date start], [date end]
Resident tabExchangeRates;
Left Join (tabData)
LOAD Distinct
[date start],
[date end],
AutoNumberHash128([date start], [date end]) as %PeriodID
Resident tabData;
DROP Fields [date start], [date end] From tabData;
hope this helps
regards
Marco