Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
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?
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;
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
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;
Thanks a lot that was it...