2 Replies Latest reply on Apr 17, 2018 10:18 PM by Shinichiro Murakami

    Data model issue


      I have a question on how to model (star schema/ flat denormalised table etc )these 2 tables for getting Region wise Revenue.


      Table 1: Sales


      1                                          5                               2                                         1                          5.6.2017

      2                                           2                                6                                       1                          5.6.2018

      Table2: Employee

      IdNameRegionRegion Start Date

      1                                    A                                 SF                       1.1.2017

      1                                    A                                  TX                       1.1.2018


      Basically SaleID 1 should map to SF region as that employee was in SF region at that time (table 2 tells that he moved to TX in 2018).so SalesID 2 should map to TX

      Hence revenue of SF should be 10 and that of TX should be 12.



      Note: I have many records in table 1 and table 2 .

        • 1. Re: Data model issue
          Paul Field

          One way is to add a colum to the your employee table with a "Date Moved". This is null if it's the employee's current location.


          Then we can create a join like:



          which will still give us the wrong result, until we add another join (if you can refer to the same column "date" again), or as I couldn't do this using excel, a data source filter:

          [Date] <= IFNULL([Date Moved], DATE('1/1/9999'))


          Set to "True"

          • 2. Re: Data model issue
            Shinichiro Murakami

            HI Saurabh


            Only available version 10.5.



            Use range join for date Sale Date >= Region Start

            There are duplication of Sale ID, then remove duplication with picking only newest date of Region Start.

            (Already filtered as smaller than Sale date)