How to lookup to nearest date based on value of other column

[mallikarjun_h]

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?

[sunny_talwar]

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

[shambhub]

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

[mallikarjun_h]

Is it possible without expanding?

[Eugeny_Ilyin]

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;