Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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