Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a table which keeps exchange rate for corresponding date.
e.g.
Date FROM TO Exchange rate
10/4/2015 INR USD 1.344
30/4/2015 INR USD 1.244
And I have another table which has
Date current currency
1/4/2015 INR
2/4/2015 INR
3/4/2015 INR
..........
30/4/2015 INR
I want to add another column in this table which will give exchange rate to convert the INR to USD
but for 1/4/2015 to 10/4/2015 I need to have 1.344 as exchange rate and for 11/4/2015 to 30/4/2015 it should be 1.244
e.g
Date current currency Exchange rate
1/4/2015 INR 1.344
2/4/2015 INR 1.344
3/4/2015 INR 1.344
..........
29/4/2015 INR 1.244
30/4/2015 INR 1.244
How to populate my table with extra 'Exchange rate' column using qlikview scripting?
I would start by expanding your table which keeps the exchange rate to have all the dates. Add all the dates and use the Peek function to get the previous available exchange rate to the newly created rows. Once you have all the date you can easily match them with your second table. To fill your exchange rate table you can refer to the document at this link: Generating Missing Data In QlikView
HTH
Best,
Sunny
Hi Prasad,
You can try like this using SQL in Script editor.
SELECT D.Date as TransDate, D.CurrentCurrency,
E.ExchangeRate, Max(E.Date) as ExchangeRateDate
FROM Details D
Left outer join CurrencyTable E on
D.Date <= E.Date
Please let me know for any clarification.
Best Regards
Shambhu B
Is it possible without expanding?
hi
maybe this script will help
table:
LOAD * INLINE [
key, cur
1, inr
2, inr
3, inr
4, inr
5, inr
6, inr
7, inr
8, inr
9, inr
10, inr
];
left join (table) LOAD key, from as cur, rate INLINE [
key, from, to, rate
4, inr, usd, 1.344
10, inr, usd, 1.244
];
result:
NoConcatenate Load key,
If(IsNull(cur), Peek(cur), cur) as cur,
if(IsNull(rate), Peek(rate), rate) as rate
Resident table
Order By key desc ;
DROP Table table;