sales with Multiple currencies

[michael_anywar]

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.

Thank you

Table 1

ID	Currency	Rate
	SEK	2
	EUR	1
	USD	1
	BRP	1

Table 2

ID	Timestamp
1	Time1
2	Time 2
2	Time 2
2

InvoiceInfor

Currency	Date	KEYID
EU
SEK
EU

Invoice Details:

KEYID	Salessum	New field(SalesSumEUR)

Highly appreciate any help offered.

[petter]

First:

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.

[michael_anywar]

Thanks Petter,

          First Table2 is updated on daily basis, hence has not time portion as much as it is called time in the model below.( as it comes direct from the European Bank Data source).

          And yes there is a 1:1 relationship between InvoiceInfor and Invoice Details. so all  my tables are already normalized.

SO basically what i was thinking of is how to identify a specific rate&time and Currency then probably put that in the invoiceDetails table. Below are my 4 table models.

Thank you. InvoiceBrideg is just a to join the two.

[petter]

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.