6 Replies Latest reply: Jul 12, 2015 9:49 AM by fatih sahin RSS

    Autogenerate currency rates

    fatih sahin

      Hi Community,

       

      just trying to integrate a master currency into my application . So far so god but I need to extend my currency table with values for the future.

       

      For example following table structure:

       

      OriginaCurrency| Reporting Currency | FX_Rate | FX_Fiscal_Year | FX_Month

      Dollar                  Euro                         2,3          2015                         5

      Dollar                  Euro                         0,9          2015                         6

      Dollar                  Euro                         0.8          2015                         7

       

       

      But I have also future data in my application therfore I need logic to extend this list - it should take the last existing FX rate for the respective curreny and generate all the rest for the rest of the year. The result should look like this than:

       

       

      OriginaCurrency| Reporting Currency | FX_Rate | FX_Fiscal_Year | FX_Month

      Dollar                  Euro                         2,3          2015                         5

      Dollar                  Euro                         0,9          2015                         6

      Dollar                  Euro                         0.8          2015                         7

      Dollar                  Euro                         0.8          2015                         8

      Dollar                  Euro                         0.8          2015                         9

      Dollar                  Euro                         0.8          2015                         10

      Dollar                  Euro                         0.8          2015                         11

      Dollar                  Euro                         0.8          2015                         12

      Dollar                  Euro                         0.8          2016                         1

      Dollar                  Euro                         0.8          2016                         2

      Dollar                  Euro                         0.8          2016                         3

      Dollar                  Euro                         0.8          2016                         4

      Dollar                  Euro                         0.8          2016                         5

      Dollar                  Euro                         0.8          2016                         6

      Dollar                  Euro                         0.8          2016                         7

      Dollar                  Euro                         0.8          2016                         8

      Dollar                  Euro                         0.8          2016                         9

      Dollar                  Euro                         0.8          2016                         10

      Dollar                  Euro                         0.8          2016                         11

      Dollar                  Euro                         0.8          2016                         12


       

      Please consider that I have multiple currency which has to be converted into three currencies - Euro,USD and AUD.

       

      Any suggestions how to do this?

       

      Regards

        • Re: Autogenerate currency rates
          Stefan Wühl

          Please check

          How to populate a sparsely populated field

          and more general

          Generating Missing Data In QlikView

           

          vMaxDate should be set to your future max date.

           

          If you have multiple currencies, you would need to to adapt the code to something like

           

             TempTable_Rates:
               Load Date, Currency, Rate From Rates ;


               MinMaxDate:
               Load Min(Date) as MinDate, Max(Date) as MaxDate resident TempTable_Rates;
               Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;
               Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate')    ;

              
               TempCurrencies:
               LOAD  Currency INLINE [

               Currency

               AUD

               USD

               EUR    

               ];

           

               Join (TempCurrencies)

               Load Date(recno()+$(vMinDate)) as Date Autogenerate vMaxDate - vMinDate;


               Join (TempTable_Rates)

               LOAD * RESIDENT TempCurrencies;


               Rates:

               NoConcatenate Load Date,
                    If( Peek(Currency) = Currency and IsNull( Rate ), Peek( Rate ), Rate as Rate
                    Resident TempTable_Rates
                    Order By Currency, Date ; /* so that above values can be propagated downwards */


               Drop Table MinMaxDate, TempTable_Rates, TempCurrencies;

            • Re: Autogenerate currency rates
              fatih sahin

              Hi Thanks,

               

              but I do not need to have the currency on daily base its  ok if it is on monthly base - also I do not have a real date field in the currency table - only the year and month.

               

              Is this enough to build the logic?

                • Re: Autogenerate currency rates
                  Stefan Wühl

                  Sure.

                   

                  TempTable_Rates:
                  LOAD * INLINE [
                  OriginalCurrency, Reporting Currency , FX_Rate , FX_Fiscal_Year , FX_Month
                  Dollar,                  Euro ,                        2.3 ,         2015   ,                      5
                  Dollar,                  Euro ,                        0.9 ,         2015    ,                     6
                  Dollar,                  Euro ,                        0.8 ,         2015    ,                     7
                  ];
                     
                  TempCurrencies:
                  LOAD  'Dollar' as OriginalCurrency, Currency as [Reporting Currency] INLINE [
                   Currency
                   AUD
                   USD
                   Euro    
                  ];
                  
                  Join (TempCurrencies)
                  LOAD Year(TmpDate) as FX_Fiscal_Year, Num(Month(TmpDate)) as FX_Month;
                  Load AddMonths(Makedate(2015,4),recno() ) as TmpDate Autogenerate 20;
                  
                  
                  
                  Join (TempTable_Rates)
                  LOAD * RESIDENT TempCurrencies;
                  
                   Rates:
                   NoConcatenate Load OriginalCurrency, [Reporting Currency],  FX_Fiscal_Year , FX_Month, 
                        If( Peek([Reporting Currency]) = [Reporting Currency] and IsNull( FX_Rate ), Peek( FX_Rate ), FX_Rate )  as FX_Rate
                   Resident TempTable_Rates
                   Order By [Reporting Currency], FX_Fiscal_Year, FX_Month ; /* so that above values can be propagated downwards */
                  
                   Drop Table TempTable_Rates, TempCurrencies;
                  
                    • Re: Autogenerate currency rates
                      fatih sahin

                      Hi Swuehl,

                       

                      that was already helpful but with that it is only for Dollar generating the generated values.

                       

                      I have more than 30 currencies how I can adjust this script that I can load more than dollar as original currency?

                       

                      Regards

                        • Re: Autogenerate currency rates
                          Stefan Wühl

                          Like this

                           

                          TempTable_Rates:
                          LOAD * INLINE [
                          OriginalCurrency, Reporting Currency , FX_Rate , FX_Fiscal_Year , FX_Month
                          Dollar,                  Euro ,                        2.3 ,         2015   ,                      5
                          Dollar,                  Euro ,                        0.9 ,         2015    ,                     6
                          Dollar,                  Euro ,                        0.8 ,         2015    ,                     7
                          ];
                          LOAD [Reporting Currency] as OriginalCurrency, OriginalCurrency as [Reporting Currency], 1/FX_Rate as FX_Rate, FX_Fiscal_Year, FX_Month
                          RESIDENT TempTable_Rates;
                            
                          TempCurrencies:
                          LOAD  DISTINCT OriginalCurrency , [Reporting Currency] 
                          RESIDENT TempTable_Rates;
                          
                          Join (TempCurrencies)
                          LOAD Year(TmpDate) as FX_Fiscal_Year, Num(Month(TmpDate)) as FX_Month;
                          Load AddMonths(Makedate(2015,4),recno() ) as TmpDate Autogenerate 20;
                          
                          Join (TempTable_Rates)
                          LOAD * RESIDENT TempCurrencies;
                          
                           Rates:
                           NoConcatenate 
                           Load OriginalCurrency, [Reporting Currency],  FX_Fiscal_Year , FX_Month, 
                                If( Peek([Reporting Currency]) = [Reporting Currency] 
                                and Peek(OriginalCurrency) = OriginalCurrency and IsNull( FX_Rate )
                                , Peek( FX_Rate )
                                , FX_Rate )  as FX_Rate
                           Resident TempTable_Rates
                           Order By OriginalCurrency, [Reporting Currency], FX_Fiscal_Year, FX_Month ; /* so that above values can be propagated downwards */
                          
                          
                           Drop Table TempTable_Rates, TempCurrencies;