Help With Table Joins and Mapping Null Fields

Wed, 12 Nov 2014 17:26:38 GMT

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;

Re: Help With Table Joins and Mapping Null Fields

Gysbert_Wassenaar — Wed, 12 Nov 2014 17:45:53 GMT

See attached qvw,

Re: Help With Table Joins and Mapping Null Fields

Wed, 12 Nov 2014 18:26:14 GMT

Very nice, thank you!

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

Re: Help With Table Joins and Mapping Null Fields

Gysbert_Wassenaar — Wed, 12 Nov 2014 18:49:57 GMT

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'.

topic Help With Table Joins and Mapping Null Fields in QlikView

Help With Table Joins and Mapping Null Fields

Re: Help With Table Joins and Mapping Null Fields

Re: Help With Table Joins and Mapping Null Fields

Re: Help With Table Joins and Mapping Null Fields