1 Reply Latest reply on Nov 15, 2017 7:53 PM by Bill Lyons

    Sharepoint lists - Blending data or Join?

    Andrew Toth

      The Goal:   To display job costing vs billing.   Starting small with just total hours through machines and display the amount we billed by Work Order Number.

       

      I am creating a sharepoint database that is connected to our quickbooks company file.  I have created some simple apps to create time tracking for certain machines in our facility.  I am trying to track our productivity and display this information along with the billing.

       

      Every job that comes through the facility has a work order number.  The time entry apps reference this work order number to allocate time to that job.  When I connect to the sharepoint data source, I used a Left join from our completed job list which has billing information, and joined it to our time entry tables using the work order reference number as the join clause.

       

      I started creating some simple visualizations but quickly realized that the join is working in a way that I did not intend.  For every time entry associated to a work order, the total billing for the job was attached.  So each time entry ended up adding a duplicate of the billing.  After researching this it looks like I need to blend the data because these two tables have a different granularity to the data.

       

      The tutorial for blending data says to add a second data source.  When I try to do this using sharepoint, it seems to just replace the original datasource instead of adding a copy.  Duplicating the data source does not seem to do anything.  It seems I do not have the ability to designate a primary data source and secondary data source as the tutorial recommends.

       

      I have only taken Tableau fundamentals 1, and the class was excellent. However the data we worked with was simple, and we did not go over joins or blending.

       

      Here is how the connection looks in the data source page:

       

       

      DataSetup.png

       

       

       

      Here is a sample of each table:

       

      TBL_CompletedJobs 

       

      TxnID          WO#          CustomerID          Customer Name          SubTotal      Job info etc.

       

      TBL_WheelHours

       

      Entry #          WO#            CustomerName          Material Hours

       

      TBL_BlastHours

       

      Entry #            WO#          CustomerName           BlastBooth Loc        Material Hours

       

       

      When I join these tables on the WO#, the subtotal of billing is included on every entry.  I definitely screwed up the join process!  I am quite a novice when it comes to database design, so I fear the issue is with how I structured the data.  The good thing is that all of this has been created in the last month, so changing something now will be easy and have little to no impact on our business yet.

       

       

      Appreciate any help you can give!

       

      Andy

        • 1. Re: Sharepoint lists - Blending data or Join?
          Bill Lyons

          I believe a Level of Detail (LoD) calculation is likely to help, but it is difficult to say exactly how this would look without seeing how you are using the data in a viz. In a LoD, In an LoD, you can specify the level of granularity on which to perform the calculation. For example, if you have duplicate [billing amount] fields for a [work order ID], then { FIXED [work order ID] : MIN([billing amount]) } will return the same billing amount, regardless of how many rows in the [work order].

           

          If you can provide a sample workbook, I can give a more specific answer. Please see Packaged workbooks: when, why, how and provide us something to work with.