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

Join transactions with currency rates

Hi All,

I got two tables - one stores transactions (TransNo, TransDate), the other stores currency rates (CurrRate, CurrDate). The tables need to be joined by date in a way that TransDate joins first previous CurrDate - as per the below example. Any idea how to join them?

Transactions:

TransNo, TransDate

1, 2012-02-03

2, 2012-02-06

CurrencyRates:

CurrDate, CurrRate

2012-02-01 , 2.2

2012-02-02 , 2.3

2012-02-03, 2.5

2012-02-06, 2.4

JoinedTables:

TransNo, TransDate, CurrDate, CurrRate

1, 2012-02-03, 2012-02-02 , 2.3

2, 2012-02-06, 2012-02-03, 2.5

Regards,

Przemek

1 Solution

Accepted Solutions
swuehl
MVP
MVP

I assume that you have CurrencyRates for all dates that you have Transactions in.

You could try something like

Transactions:

LOAD * INLINE [

TransNo, TransDate

1, 2012-02-03

2, 2012-02-06

];

CurrencyRates:

LOAD * INLINE [

CurrDate, CurrRate

2012-02-01 , 2.2

2012-02-02 , 2.3

2012-02-03, 2.5

2012-02-06, 2.4

];

Left join (Transactions) LOAD CurrDate, CurrRate, Peek(CurrDate) as TransDate

resident CurrencyRates order by CurrDate desc;

 

drop table CurrencyRates;

View solution in original post

2 Replies
swuehl
MVP
MVP

I assume that you have CurrencyRates for all dates that you have Transactions in.

You could try something like

Transactions:

LOAD * INLINE [

TransNo, TransDate

1, 2012-02-03

2, 2012-02-06

];

CurrencyRates:

LOAD * INLINE [

CurrDate, CurrRate

2012-02-01 , 2.2

2012-02-02 , 2.3

2012-02-03, 2.5

2012-02-06, 2.4

];

Left join (Transactions) LOAD CurrDate, CurrRate, Peek(CurrDate) as TransDate

resident CurrencyRates order by CurrDate desc;

 

drop table CurrencyRates;

Not applicable
Author

Thank you, Stephan. It appears that your the one who answers most of my questions here! Perhaps you could help with this one, too (still not answered): http://community.qlik.com/message/187927#187927