1 Reply Latest reply on Oct 6, 2016 9:44 AM by Joe Oppelt

    Combining Data Sources and Creating Calculated Fields

    Peter Bova

      Hello,

       

      I have two data sources which I have combined via blending. The second data source is a reference table. In the primary data source I have individual events that I am tracking with pricing information for each event. Each event has a billing status and based on that status and a few other things, I am trying to calculate the prices based on whether or not the event is complete, and if it is not complete then I use the reference table to multiple a percentage with the price of that event. Example:

       

      Event (unique #)
      Plan
      Type
      Complete
      Price
      1ABCAYES5
      2XYZBYES4
      3DEFAYES4
      4ABCBNO3
      5DEFBYES2
      6DEFANO6
      7XYZANO7

       

      Then my reference table would look something like this:

       

      PlanType
      Factor
      ABCA0.75
      ABCB0.67
      DEFA0.56
      DEFB0.50
      XYZA0.80
      XYZB0.75

       

      So if the event is not complete, then I want the price in the original data source to be multiplied by the factor. Then I need to aggregate data by plan and sum the total dollar amount for the prices of the events for that plan. In my current view, it is not summing the data properly. It seems like the Factor from the reference table is coming in as an aggregate field and that it is not actually joined to my primary data source as another column of data. Thus, I can't even look at the underlying data and see where the calculation is going wrong.

       

      Please help! Thanks!    

        • 1. Re: Combining Data Sources and Creating Calculated Fields
          Joe Oppelt

          Everything coming from a secondary blended data source has to come as an aggregated value.

           

          If you have only one row for the join-field, then SUM([Value]) will give you that value for each key value in your primary source.


          So if you are blending on [Plan] and [Type], then SUM([secondary].[Factor]) = 0.75 for ABC-A in your underlying data table.  (If there were two rows for ABC-A in the secondary source, it would sum the two [Factor] values together.  If you don't want them summed, you could instead use MAX() or MIN() or AVG() instead of SUM.)

           

          If you want, you could create a calc field in your primary source that does the SUM([secondary].[Factor], but it's not really necessary.