Handling Nulls after concatenation

[#1]

Hi,

I'm dealing with 2 fairly similar tables, although not identical with regard to column names (most of the columns are present in both tables). In the end, I'm concatenating the 2 tables to create a 'master' data table, on which my analysis will be made. The problem is with the not-in-common columns, which appear on one of the original tables but not on the other. By default, after concatenation the values there will be NULL. Is there a way to autofill these NULL columns with a value ("Unknown" will do)?

For example:

Table 1:

Vehicle	A	B
1	value1	value2

Table 2:

Vehicle	A	C
2	value3	value4

After concatenating the 2 tables, I'll get:

Vehicle	A	B	C
1	value1	value2
2	value3		value4

With NULLS in some cells on columns B & C. I want to autofill these cells with "unknown".

[#2]

Sure thing, here is the method:

  Re: How to fill in blank or null values after outer join

 

      jason.michaelides    Jul 6, 2012 4:15 AM  (in response to Francois de Wet)  

I don't like having any NULLs in my data model as they are difficult to work with and not selectable.  I normally replace NULLs with '<Unknown>' or something similar.

MAP...USING works well:

Map_Null_Unknown:

MAPPING LOAD

    Null()

    ,'<Unknown>'

Autogenerate 1;

MAP Field1,Field2,Field3,etc USING Map_Null_Unknown;

Now all nulls listed in the MAP...USING statement will be replaced as they are loaded.  However, nulls created by LEFT/OUTER joins will not be replaced, unless that field is subsequently loaded later in the script. If your joined fields with nulls are not loaded again (by some kind of resident load) then you will need to force it.  This works well:

RIGHT JOIN (Table) LOAD DISTINCT * RESIDENT Table;

[#3]

no worries, glad to help

[mmarchese]

Wouldn't this work and be the simplest?  Why bother loading the data a second time just to replace the nulls?

LOAD
    Vehicle,
    A,
    B,
    'Unknown' AS C
FROM Table1;

LOAD
    Vehicle,
    A,
    'Unknown' AS B,
    C
FROM Table2;