3 Replies Latest reply: Apr 6, 2018 1:48 AM by Petter Skjolden RSS

    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

      IDCurrencyRate
      SEK2
      EUR1
      USD1
      BRP1

      Table 2

      IDTimestamp
      1Time1
      2Time 2
      2Time 2
      2

       

      InvoiceInfor

      CurrencyDateKEYID
      EU
      SEK
      EU

       

      Invoice Details:

      KEYIDSalessumNew field(SalesSumEUR)

       

      Highly appreciate any help offered.

        • Re: sales with Multiple currencies
          Petter Skjolden

          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.

            • Re: sales with Multiple currencies
              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.

              New Picture (1).bmp

                • Re: sales with Multiple currencies
                  Petter Skjolden

                  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.