3 Replies Latest reply: Nov 12, 2014 1:50 PM by Gysbert Wassenaar RSS

    Help With Table Joins and Mapping Null Fields

      I am trying to accomplish something that is very simple with SQL Server.  I have two tables that I want to join, and I want to default values when they don't exist in the second table.

       

      For example:

       

      SELECT

      c.COST_CENTER_ID,

      ISNULL(m.[Cost Center], 'Other') AS [Cost Center],

      ISNULL(m.[Sub Cost Center],COST_CENTER_DESC) AS  [Sub Cost Center],

      c.COST_CENTER_DESC

      FROM CostCenter c

        LEFT OUTER JOIN CC_Map m ON c.COST_CENTER_ID = m.COST_CENTER_ID;

       

      I have found a work around in Qlikview to get this result, but it seems really awkward and cumbersome.  I was hoping someone could show me a better way.

       

      NullMapCC:

      MAPPING LOAD null(), 'Other' AUTOGENERATE 1;

      MAP [Cost Center] USING NullMapCC;

       

       

      CostCenter:

      LOAD * INLINE [

          COST_CENTER_ID, COST_CENTER_DESC

          46258, AFAS INSPECTIONS

          46266, AFAS PROPERTY PRESERVATION

          46254, AFAS REHAB

          46256, AFAS HOA

          46255, AFAS REGISTRATIONS

          46264, AFAS RELOCATIONS

          46265, AFAS UTILITIES

          46253, AFAS EXECUTIVE MANAGEMENT

          46259, AFAS FINANCE AND ACCOUNTING

      ];

       

       

      Join (CostCenter)

      LOAD * INLINE [

          COST_CENTER_ID, Cost Center, Sub Cost Center

          46258, Inspections, Inspections

          46266, Property Preservation, Property Preservation

          46254, Rehab, Rehab

          46256, Ancilary Services, HOA

          46255, Ancilary Services, Registrations

          46264, Ancilary Services, Relocations

          46265, Ancilary Services, Utilities

      ];

       

      CostCenter2:

      NoConcatenate

      Load *

      Resident CostCenter;

       

      drop table CostCenter;

       

      CostCenter:

      NoConcatenate

      Load

      COST_CENTER_ID,

      [Cost Center],

      IF([Cost Center]='Other', COST_CENTER_DESC, [Sub Cost Center]) as [Sub Cost Center],

      COST_CENTER_DESC

      Resident CostCenter2;

       

       

      Drop Table CostCenter2;