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]

The DATAAREAID's do not match since one is 15 the other in currency is 10 then the first LEFT JOIN do not append the currencycode to the transaction table and you need it for the interval match.

When this issue is solved put and EXIT SCRIPT; after the tmp_Rates load statement in order to see of the TODATE is computed correctly as well.

Let's go to sleep now...

[#3]

Yes, that is correct

That is why we have to go through the COMPANYINFO table.  The COMPANYINFO table states that DATAAREAID = 15 in PROJTRANSPOSTING equals CURRENCYCODE = DKK

Thanks for your help so far.  I am understating more and more.

Have a good night.

Regards

Thor

[#4]

Hi Michaël Laenen

Do you do paid work as well?  I would love to get help to get the currency issue to work, but also to get help with incremental load.

I am more than happy to pay for this

Regards

Thor

[agilos_mla · Accepted solution]

Hi Thorn,

I took some minutes to look deeper at your sample.

Please find hereafter a new version of the qvw. It worked for me.

I do not paid work but I'm please to help you finishing the currency case. I'll send you in a private email the coordinates of a person that do paid work for us at very interesting rates.

Rgds,

Michael

[#6]

Fantastic Michaël Laenen.  Works perfectly.

Thanks also for the email.  I truly appreciate it.

Thanks again for all your help.

Regards

Thor