Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Join transactions with currency rates

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;

2 Replies
MVP
MVP

Join transactions with currency rates

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

Join transactions with currency rates

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

Community Browser