I have basically 4 tables already created. two containing Currency and rates details, while the other contains invoice details. I would like to create a new field using Data Manager to contain SaleSum in EUR basing on the exchange rate of when the date of Table2 = Date(Timestampe) in Table invoiceInfor.
But any other option if possible, i will take it on.
I would recommend to have a table called CURRENCY that would be the join of Table1 and Table2. In this table I would create a key based on Currency and Date of Timestamp called CURKEY. I would then create a matching key in the InvoiceInfo table based on Currency and Date also called CURKEY.
Then you should have a valid associative model where you can use this expression in your charts:
Sum( Salessum*Rate ) on the assumption that a the InvoiceInfo match one by one on the row level.
If you have some valid example data it would help removing any assumptions that might be wrong like:
Is the timestamp of Table2 really a date without a time portion?
Is there multiple entries for one currency for any single day in Table2?
Is it a 1:1 relationship between the InvoiceFor and the Invoice Details tables?
If you have the time:
Normally I would simplify the table structure (data model) by joining Table1 and Table2 into a single Currency table.
Secondly I would try to do the same with InvoiceFor and Invoice Details unless there are strong reasons not to.
The rule of thumb with Qlik data models is that a dimensional approach with a star schema that is denormalized is the best approach. Your tables seem to come directly from a source system with a normalized table structure.
If there is a 1:1 relationship between the InvoiceInfor and InvoiceDetails tables then a bridge table is completely unnecessary. However if the relationship is 1:M then there is a need for the bridge table unless you are willing to accept duplicate rows in the InvoiceInfor table to match each InvoiceDetails rows.
Still I would prefer to denormalize as much as possible.