Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

matching currency exchange rate

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

6 Replies
Not applicable
Author

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.

Not applicable
Author

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.

Not applicable
Author

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

martynlloyd
Partner - Creator III
Partner - Creator III

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

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

-Rob

MarcoWedel

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;

QlikCommunity_Thread_130134_Pic3.JPG.jpg

QlikCommunity_Thread_130134_Pic2.JPG.jpg

QlikCommunity_Thread_130134_Pic1.JPG.jpg

hope this helps

regards

Marco