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

    Data model issue

    saurabh.c.0

      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

      SaleIDProductPriceQuantityEmployeeIDDate

      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)

             

             

             

            Thanks,

            Shin