Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
See attached qvw,
Very nice, thank you!
How does if(len(trim([Cost Center])),[Cost Center], 'Other') evaluate? if the field is null it returns false?
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'.