topic Re: matching currency exchange rate in QlikView

matching currency exchange rate

Wed, 20 Aug 2014 14:31:05 GMT

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

Re: matching currency exchange rate

Wed, 20 Aug 2014 14:46:02 GMT

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.

Re: matching currency exchange rate

Wed, 20 Aug 2014 15:35:09 GMT

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.

Re: matching currency exchange rate

Wed, 20 Aug 2014 16:48:05 GMT

Please Share the some sample data so I will give some script solution for this.

Re: matching currency exchange rate

martynlloyd — Wed, 20 Aug 2014 17:19:06 GMT

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,

Re: Re: matching currency exchange rate

rwunderlich — Wed, 20 Aug 2014 17:29:02 GMT

It's a one-liner with Qlikview Components http://qlikviewcomponents.org

CALL Qvc.ExpandInterval ('RateTable', 'RateDate')

-Rob

Re: matching currency exchange rate

MarcoWedel — Wed, 20 Aug 2014 23:24:25 GMT

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