Discussion Board for collaboration on QlikView Scripting.
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.
ISNULL(m.[Cost Center], 'Other') AS [Cost Center],
ISNULL(m.[Sub Cost Center],COST_CENTER_DESC) AS [Sub Cost Center],
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.
MAPPING LOAD null(), 'Other' AUTOGENERATE 1;
MAP [Cost Center] USING NullMapCC;
LOAD * INLINE [
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
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
drop table CostCenter;
IF([Cost Center]='Other', COST_CENTER_DESC, [Sub Cost Center]) as [Sub Cost Center],
Drop Table CostCenter2;
Go to Solution.
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'.