Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Autofill date upto a range

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

1 Solution

Accepted Solutions

Re: Autofill date upto a range

See attached example.


talk is cheap, supply exceeds demand
4 Replies
Not applicable

Re: Autofill date upto a range

Hi Sid,

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

and see page 7, Conversion rates.

Good luck!

Rainer

Not applicable

Re: Autofill date upto a range

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!

Re: Autofill date upto a range

See attached example.


talk is cheap, supply exceeds demand
Not applicable

Re: Autofill date upto a range

Thank you Gysbert. I appreciate it.

Community Browser