Outer Join then Replace Null Values

[#1]

I’ve joined the below two tables, with the outcome being a “GP_Dimension” table that now includes a “GPflag” field (a field that has “Y” or “N”).  However, if a gpNum isn’t on the GPflag excel sheet, rather than it being null in the resulting "GP_Dimension" table, I’d like it default to “Y”.  How do I replace a null w/ a specified character in the load script, in this case “Y”?

LOAD gpNum,
     regionCode,
     regionName
     FROM
Q:\\GP_Dimension.QVD
(qvd);

Outer Join (GP_Dimension)

LOAD gpNum,
     Gpflag

FROM
[my source]
(biff, embedded labels, table is Sheet1$);

[ToniKautto]

I would go for loading the table into a new table to replace the null values, and then drop the initial table.

[#3]

Why not you use applymap instead to make the outer join?

[ToniKautto]

How do you suggest that, consider the two different source tables? The Null value does not exist until after the join, so you could not map to it until afterwards?

[#5]

The second table should be load first with a mapping load and then use this map when load the first table and the default value of 'Y'. I this way no null will be generated.

[#6]

"tableMap":

MAPPING LOAD gpNum,
     Gpflag

FROM
[my source]
(biff, embedded labels, table is Sheet1$);

LOAD gpNum,
     regionCode,
     regionName

     APPLYMAP('tableMap', gpNum, 'Y') AS Gpflag
     FROM
Q:\\GP_Dimension.QVD
(qvd);

[rwunderlich · Accepted solution]

It's possible to replace or default nulls using a map like this:

NullMap:

MAPPING LOAD null(), 'Y' AUTOGENERATE 1;

MAP GPflag USING NullMap;

-Rob

http://robwunderlich.com

[Anonymous]

Can you please let me know where i can use this Mapping ...Using ..before or after the Outer Join statement?

[rwunderlich]

Before.

[pentaxadmin]

Thanks, it works like a magic. I was looking for this solution earlier.