Autofill date upto a range

[#1]

Hi,

I have a table for currency conversion that looks below, These are the only fields that this table have.

Valid from            From currency               To Currency             Exchange Rate

03/30/2013            GBP                                 USD                           1.5152

03/30/2013            EURO                              USD                           1.2798

04/27/2013            GBP                                 USD                           1.5373

04/27/2013            EURO                              USD                            1.2646

05/31/2013            GBP                                 USD                            1.5165

05/31/2013            EURO                              USD                            1.2981

06/28/2013            GBP                                 USD                            1.5291

06/28/2013            EURO                              USD                             1.2702

The second table is transaction table that has transaction lile items as follows:

Transaction date         Order number       Amount          Currency Code

04/04/2013                    400003456             2,236.00          GBP

04/23/2013                    400003488             2,200.00          EURO

05/11/2013                    400003459             1039.00           GBP

06/15/2013                    400003460             4400.00            EURO

07/12/2013                     400003478             3713.00           GBP

I need to join these two table to convert the amount in transaction table to USD but I am stuck. Please suggest how it could be done. One possible solution that i though of is if I can autofil the date range in currency conversion table (and then join it with transaction table on date field) to look like below:

Date                 From currency               To Currency                        Exchange Rate

03/30/2013                 GBP                        USD                                     1.5152

03/30/2013                 EURO                     USD                                     1.2798

03/31/2013                 GBP                        USD                                     1.5152

03/31/2013                 EURO                     USD                                     1.2798

04/01/2013                 GBP                        USD                                     1.5152

04/01/2013                 EURO                     USD                                     1.2798

.

.

.

04/26/2013                 GBP                         USD                                     1.5152

04/26/2013                 EURO                      USD                                     1.2798

04/27/2013                 GBP                         USD                                     1.5373

04/27/2013                 EURO                      USD                                     1.2646

04/28/2013                 GBP                         USD                                     1.5373

04/28/2013                 EURO                      USD                                     1.2646

04/29/2013                 GBP                         USD                                     1.5373

04/29/2013                 EURO                      USD                                     1.2646

.

.

.

05/30/2013                 GBP                         USD                                     1.5373

05/30/2013                 EURO                      USD                                     1.2646

05/31/2013                 GBP                         USD                                     1.5165

05/31/2013                 EURO                      USD                                     1.2981

06/01/2013                 GBP                         USD                                     1.5165

06/01/2013                 EURO                      USD                                     1.2981

06/02/2013                 GBP                         USD                                     1.5165

06/02/2013                 EURO                      USD                                     1.2981

.

.

.

and so on...

Is this I can do in qlikview? If yes I will appreciate any suggestions on how it can be done. If not, is there any other way the amount can be converted to USD? Thanks in advance for helping.

Best regards,

Sid

[#2]

Hi Sid,

please follow this link: http://community.qlik.com/docs/DOC-3786

and see page 7, Conversion rates.

Good luck!

Rainer

[#3]

Thats a great document. Thanks for ponting to that. However the algorithm in the document is not considering multiple currency codes. The clomuns mentioned in the algorithm are 1. rates & 2. Dates. When I am trying to "fit in"  third column called "from currency code" (" to currency is already defaultd to USD) it gives me blank value. I think to insert it it might need some changes but not sure how to do them . Can you please help. I will appreciate that. Thanks!

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[#5]

Thank you Gysbert. I appreciate it.