Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

fatihsahin
New Contributor

Autogenerate currency rates

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Autogenerate currency rates

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;

6 Replies
MVP
MVP

Re: Autogenerate currency rates

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
New Contributor

Re: Autogenerate currency rates

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?

MVP
MVP

Re: Autogenerate currency rates

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
New Contributor

Re: Autogenerate currency rates

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

MVP
MVP

Re: Autogenerate currency rates

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
New Contributor

Re: Autogenerate currency rates

Thanks a lot that was it...

Community Browser