4 Replies Latest reply on Sep 20, 2018 12:40 PM by Deepak Rai

    which type of join should I use to get the correct calculations from this table and filtering?

    a m

      Hi everyone,

       

      I am wondering which type of join (or maybe I should use a union) I can use to correctly use the data the way I want for filtering and graphs. I have a large data file and I am having trouble figuring this issue out. Below I made a simple example of two data sets I  have. the finance table and the employee table.

       

       

      finance table

       

      place

      transitactual
      forecast
      Header 5
      store200105jan
      warehouse5002010feb
      store10055jan

                                                      total

       

       

      employee table

       

      worker
      worker number
      placemonthtransitemployee type
      tim1storejan200a
      jane2storejan200a
      jo3warehousefeb500b
      jim4warehousefeb500b
      yoda10storejan100a

       

       

       

       

       

      Joined

       

      worker
      worker number
      placemonthtransitemployee typeactual
      forecast
      tim1storejan200a105
      jane2storejan200a105
      jo3warehousefeb500b2010
      jim4warehousefeb500b2010
      yoda10storejan10a55

       

       

      As you can see  when joined the actual and forecast columns duplicate the data for all employees that are under the same transit, month and place. so the totals for Jan for example won't be correct. It will show that Jan store transit 200 actual total = 20 and forecast  total = 10 not the correct way which is Jan store transit 200 actual total = 10 and forecast = 5

       

      I want to use the correct actual and forecast numbers and their totals for text tables  for calculated  column variances and the correct monthly totals for graphs.

       

      is there something I am not selecting when making text tables and graphs? or should I make a union which would make this all easier except for making all the columns in the finance table (haha)?

       

       

      thanks hope I am making sense