topic Re: Last Month Values in QlikView

Last Month Values

alvinford — Sat, 14 Jan 2017 04:06:04 GMT

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

Re: Last Month Values

hector_munoz — Sat, 14 Jan 2017 08:07:10 GMT

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

Re: Last Month Values

rahulpawarb — Sat, 14 Jan 2017 09:30:33 GMT

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

Re: Last Month Values

sunny_talwar — Sat, 14 Jan 2017 12:21:47 GMT

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)

Re: Last Month Values

praveen_prithiviraj — Mon, 16 Jan 2017 05:04:13 GMT

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);