4 Replies Latest reply: Aug 14, 2013 3:32 PM by Siddhatrth Choudhary RSS

    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