5 Replies Latest reply on Feb 1, 2019 9:20 AM by Deepak Rai

    Join at different level if null

    John McCall

      I'm attempting to join two tables, Actual Sales vs. Plan.

       

      I have Actual Sales at the Category level:

      Table 1 - Actual Sales  

      DepartmentCategorySales
      FurnitureChairs & Chairmats5947
      FurnitureOffice Furnishings5679
      FurnitureTables9382
      FurnitureBookcases4167
      TechnologyOffice Machines2476
      TechnologyComputer Peripherals9231
      TechnologyTelephones and Communication9837
      TechnologyCopiers and Fax4870

       

      I have Plan at the Category level for some categories, but only at the Department level for others:

      Table 2 - Plan

      DepartmentCategoryPlan
      FurnitureChairs & Chairmats5000
      FurnitureOffice Furnishings6000
      FurnitureTables10000
      FurnitureBookcases3000
      Technologynull25000

       

      Can I do a join on Category, unless it is null, then join on Department?  I tried using a join calculation with an IFNULL statement on both sides, but it looks like it is still just joining at the Category level:

       

      The goal would be to able to view Sales vs. Plan at the Department level (summing everything at the Category level), but then have the ability to drill into Category when applicable.

       

      Is there a better way to go about this?

       

      Thanks!