Multiply two fields from different tables via a lookup in a third table

[#1]

Hi guys,

I have got three tables:

EXCHRATES - where Exchange rates are stored with the following fields:

     - FROMDATE - the start date from when the rate is valid

     - EXCHRATE - the rate to be used from the date above until the new FROMDATE

     - TODATE - not used

     - CURRENCYCODE - The currency code for the exchange rate

COMPANYINFO - displays what the local currency for the company is

     - DATAAREAID - the ID for the company

     - CURRENCYCODE - Currency code which the company uses

PROJTRANSPOSTING - Table of transactions

     - LEDGERTRANSDATE - transaction posting date

     - AMOUNTMST - amount in local currency for the company id

     - DATAAREAID - company id

What I would like to do is to add a new field in the table PROJTRANSPOSTING which should calculate the following:

- AMOUNTMST * EXCHRATE valid for the date of the LEDGERTRANSDATE and the CURRENCYCODE of the DATAAREAID

I have attached a print screen of the table view in my Qlikview file.  As you can see on line one of the PROJTRANSPOSTING DATAAREAID is 15, which means the CURRENCYCODE to be used according to the COMPANYINFO table is DKK.  The LEDGERTRANSDATE is 30.11.2013 so the EXCHRATE to be selected from EXCHRATES table should be 118,030000 since 30.11.2013 is between 01.11.2013 and 01.12.2013 which is the next valid exchange rate for DKK.

So the new field in PROJTRANSPOSTING, should be: 

-464,84 * 118,03 / 100 = -548,65

Any idea how I can do this?  I have attached the qvw file as well, if any of you want to do me a monster favor

Best regards

Thor Hansen

[agilos_mla]

You should use the intervalmatch extended.

Please look at the attached sample.

Michael

[#3]

Hi Michaël Laenen

Thanks for the feedback and suggestion.

I am unfortunately too much of a rookie to understand what is going on in your script example.

Could I ask a huge favor from you and show what you mean in my script below?  Especially the interval part of this is a bit confusing to me.

Regards

Thor

LOAD LEDGERTRANSDATE,
month(LEDGERTRANSDATE) as MONTH,
year(LEDGERTRANSDATE) as YEAR,
     PROJID,
     AMOUNTMST,
     ACCOUNT,
     DATAAREAID;
SQL SELECT *
FROM "Dynamics_2009".dbo.PROJTRANSPOSTING;


LOAD FROMDATE,
     EXCHRATE,
     TODATE,
     CURRENCYCODE;
SQL SELECT *
FROM "Dynamics_2009".dbo.EXCHRATES where DATAAREAID = 10;

LOAD CURRENCYCODE,
     COUNTRYREGIONID,
     DATAAREAID;
SQL SELECT *
FROM "Dynamics_2009".dbo.COMPANYINFO;
 

[agilos_mla]

Please find attached script. Just hope there is not typing errors.

The resulting model should give you the ability to perform the computation as required.

Michael

[#5]

Hi Michaël Laenen

Thanks again.  No typos in there

But I dont think it will work.  From what I understand, you are looking at FROM and TO dates for the exchange rates, but there is not always a FROM date which will match with TRANSDATE.

For example, if TRANSDATE = 14.11.2013 then Exchange rate that should be used should be the one where FROMDATE is 01.11.2013 if the next FROMDATE is 01.12.2013.

Also, what is the field in PROJTRANSPOSTING which has the AMOUNTMST * EXCHRATE value?

Regards

Thor

[agilos_mla]

That's the job of interval match function to find the best record in the exchange rates tables with the condition  FROMDATE <= TransactionDate < TODATE, for every courrencycode

Then from the interval match Table, you bring back in the Transaction Table the normally unique %KEY to link with the exchange rates tables.

Then you can in the design do the computation by pure associativity, or in the script by reading the resident Transaction Table again an do the multiplication. See attached.

PS. Do not forget to drop the table temp in the script otherwwise

[agilos_mla]

Thor,

I just noted that the TODATE was 01.01.1900 in your sample, obviously the interval match cannot work in this case. Let's review a new version in attachement for which I write a piece of code to compute the TODATE correctly .

Michael

[#8]

Thanks again for helping me out Michaël Laenen

I am getting this error when I run the script:

[agilos_mla]

Please add this code before the RENAME statement;

LEFT JOIN (PROJTRANSPOSTING)

LOAD *

Resident tmp_Rates;

DROP TABLE tmp_Rates;

[#10]

No error this time when loading, but no Exchange rate in the table either.

I notice that you are considering the TODATE.  As you say yourself, it is always 01.01.1900, so we only need to look at the FROMDATE to match with the TRANSDATE.  Is this the problem?

SET ThousandSep=' ';
SET DecimalSep=',';
SET MoneyThousandSep=' ';
SET MoneyDecimalSep=',';
SET MoneyFormat='kr # ##0,00;kr -# ##0,00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='jan;feb;mar;apr;mai;jun;jul;aug;sep;okt;nov;des';
SET DayNames='ma;ti;on;to;fr;lø;sø';

OLEDB CONNECT TO [Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;User ID="";Initial Catalog=Dynamics_2009;Data Source=hbg-sql2008;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=HBG-THORH;Initial File Name="";Use Encryption for Data=False;Tag with column collation when possible=False;MARS Connection=False;DataTypeCompatibility=0;Trust Server Certificate=False];
LOAD LEDGERTRANSDATE,
month(LEDGERTRANSDATE) as MONTH,
year(LEDGERTRANSDATE) as YEAR,
     PROJID,
     AMOUNTMST,
     ACCOUNT,
     DATAAREAID;
SQL SELECT *
FROM "Dynamics_2009".dbo.PROJTRANSPOSTING where YEAR(ledgertransdate) = '2013' and month(ledgertransdate) = '11' and DATAAREAID = '15';


LEFT JOIN (PROJTRANSPOSTING)
LOAD CURRENCYCODE,
     COUNTRYREGIONID,
     DATAAREAID;
SQL SELECT *
FROM "Dynamics_2009".dbo.COMPANYINFO;


LOAD
//AutoNumber(FROMDATE & TODATE & CURRENCYCODE) AS %KEY,
FROMDATE,
     EXCHRATE,
     TODATE,
     CURRENCYCODE;
SQL SELECT *
FROM "Dynamics_2009".dbo.EXCHRATES where DATAAREAID = 10
ORDER BY CURRENCYCODE, FROMDATE desc;


tmp_Rates:
NoConcatenate
LOAD AutoNumber(FROMDATE & TODATE & CURRENCYCODE) AS %KEY,
*;
LOAD
     FROMDATE,
     EXCHRATE,
     if(Previous(CURRENCYCODE)<>CURRENCYCODE,  Date(Peek(FROMDATE)-1), Date(MakeDate(9999))) AS TODATE, 
     CURRENCYCODE
Resident EXCHRATES;
DROP Table EXCHRATES;


temp:
IntervalMatch(LEDGERTRANSDATE, CURRENCYCODE)
LOAD FROMDATE, TODATE, CURRENCYCODE Resident tmp_Rates;

LEFT JOIN (PROJTRANSPOSTING)
LOAD CURRENCYCODE,
LEDGERTRANSDATE,
AutoNumber(FROMDATE & TODATE & CURRENCYCODE) AS %KEY
Resident temp;
Drop Table temp;

LEFT JOIN (PROJTRANSPOSTING)
LOAD *
Resident tmp_Rates;
DROP TABLE tmp_Rates;

RENAME TABLE PROJTRANSPOSTING to temp;
PROJTRANSPOSTING:
NoConcatenate
LOAD AMOUNTMST*-1/100 * EXCHRATE AS Amount,
*
Resident temp;
DRop Table temp;