Help With Table Joins and Mapping Null Fields

[#1]

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;

[Gysbert_Wassenaar · Accepted solution]

See attached qvw,

[#3]

Very nice, thank you!

How does if(len(trim([Cost Center])),[Cost Center], 'Other') evaluate? if the field is null it returns false?

[Gysbert_Wassenaar]

Yes, that's correct. If the value of a field is null or contains only spaces then the length of the value after applying the trim function will be zero. And zero is the value for 'false'.