Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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$);
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
I would go for loading the table into a new table to replace the null values, and then drop the initial table.
Why not you use applymap instead to make the outer join?
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?
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.
"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);
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
Can you please let me know where i can use this Mapping ...Using ..before or after the Outer Join statement?
Before.
Thanks, it works like a magic. I was looking for this solution earlier.