2 Replies Latest reply on Feb 9, 2016 6:42 AM by Ramz Aftab

    Different result from Blending Excel/SQL Db and Joining Excel file

    Ramz Aftab

      Hello Experts,

       

      In this scenario, Im blending data from (A) SQL table and (B) Excel tab. I goto Data Relationship, edit it, and use Key to create relationship. I throw sales amount from Source (A) and Products Hierarchy from Source (B). For the amount, I use allocation where I use a Calculated field to multiply Source A dimension x Source B dimension. At this point, I encounter invalid

      formula/error. I simply want to multiple amount by an allocation factor and simple formula s/be " [Amount] * [Alloc Factor]" but it fails to accept it, and Im forced to do add ATTR and ZN fx before this formula. Apparently, the numbers are correctly calculated, but the calculations/totals and subtotals are absolutely wrong.

       

      Later, I merged the Source (A) data and Source (B) data into Excel and load the data, use JOIN (inner join) to create relationship. Again, I use this data to create calculated field and result is perfect this time. I'm astounded as to why the Data blending shows incorrect totals and Joins show correct outcome.

       

      Can someone please explain as to why Im encountering different outcome using both of the above approach. The later shows right result, but Im bound to use former one as my primary data comes from live SQL tables.

       

       

      Thanks for help,

       

      Ramz