Last Month Values

[alvinford]

Hi Everyone,

I have requirement in currency conversion. I have data (Amount.xls) and currency conversion (Conversion Rate.xls). Have to use the previous  month Exchange Rate values for the current month amount. Want to do it at script level . How do I achieve it.

Your help is appreciated. Please refer the attached excel.

Thanks & Regards,

Alvin

[hector_munoz]

Hi alvinford,

Please try the following script code:

// Mapping table with combinations of dates and exchange rates

MAP_ER:

MAPPING LOAD Date#(Date, 'YYYYMM') AS [MAP_ER Date],

             RATE                  AS [MAP_ER Rate]

FROM         [Exchange Rate.xls] (biff, embedded labels, table is Sheet1$);

// Fact table with date, amount, exchange rate (taken from previous mapping table) and amount * exchange rate fields

AMOUNTS:

LOAD *,

     Amount * [Exchange Rate] AS [Amount ER];

LOAD Date                                                        AS Date,

     Amount                                                      AS Amount,

     ApplyMap('MAP_ER', AddMonths(Date#(Date, 'YYYYMM'), -1), 0) AS [Exchange Rate]

FROM Amount.xls (biff, embedded labels, table is Sheet1$);

Notice than source files are in the same folder as than the QV document.

Regards from Spain,
Héctor

[rahulpawarb]

Hello Alvin,

Trust you are good.

Please refer below code snipped to resolve your queries.

//Load Amount table
Amount:
LOAD Date, 
     Amount
FROM
Amount.xls
(biff, embedded labels, table is Sheet1$)
Where Len(Date) > 0;

//Load Exchange Rate values
//Rename Date field because we would like to join Amount table with ExchangeRate table 
//based on prior month of the date present in Amount table
//Create new field from Date field to show prior month of the date present in Amount table
ExchangeRate:
LOAD Date AS ExchangeRateDate,  
     If(Right(Date,2) = 01, Date -89, Date -1) AS Date, 
     If(Len(RATE)>0, RATE, 1) AS RATE
FROM
[Exchange Rate.xls]
(biff, embedded labels, table is Sheet1$)
Where Len(RATE) > 0;

At Expression Level you can use below:

Sum(Amount * If(Len(RATE)>0, RATE, 1))

Let me know if you have further queries.

Thank you!

Rahul

[sunny_talwar · Accepted solution]

You are sharing already aggregated data which is probably not the best way to do things, but having said that logic can remain the same.

AmtTable:

LOAD Date(Date#(Date,  'YYYYMM'), 'YYYYMM') as Date,

    Amount

FROM

[..\..\..\Downloads\Amount.xls]

(biff, embedded labels, table is Sheet1$)

Where Len(Trim(Date)) > 0;

Left Join(AmtTable)

LOAD Date(AddMonths(Date#(Date,  'YYYYMM'), 1), 'YYYYMM') as Date,

    RATE

FROM

[..\..\..\Downloads\Exchange Rate.xls]

(biff, embedded labels, table is Sheet1$)

Where Len(Trim(RATE)) > 0;

In the above script, I am Left Joining the Exchange Rates into AmtTable, but you can keep it as a separate table (and use Left Keep)

[praveen_prithiviraj]

Try this...

tab1:

LOAD Date,

     RATE    

FROM

[Exchange Rate.xls]

(biff, embedded labels, table is Sheet1$);

tab2:

LOAD Date,

     Amount,

     Lookup('RATE','Date', [Date], 'tab1') as ExRate

FROM

[Amount.xls]

(biff, embedded labels, table is Sheet1$);

STORE tab2 into main.qvd(qvd);

DROP Table tab1;

DROP Table tab2;

tab3:

LOAD Date,

     Amount,

     ExRate    

from main.qvd(qvd);