Autogenerate currency rates

[fatihsahin]

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

[swuehl]

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;

[fatihsahin]

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?

[swuehl]

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;

[fatihsahin]

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

[swuehl · Accepted solution]

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;

[fatihsahin]

Thanks a lot that was it...