topic Re: Outer Join then Replace Null Values in QlikView

Outer Join then Replace Null Values

Wed, 23 Nov 2011 20:01:51 GMT

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$);

Re: Outer Join then Replace Null Values

ToniKautto — Wed, 23 Nov 2011 22:01:42 GMT

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

Re: Outer Join then Replace Null Values

Wed, 23 Nov 2011 22:21:42 GMT

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

Re: Outer Join then Replace Null Values

ToniKautto — Wed, 23 Nov 2011 22:26:35 GMT

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?

Re: Outer Join then Replace Null Values

Wed, 23 Nov 2011 22:46:51 GMT

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.

Re: Outer Join then Replace Null Values

Wed, 23 Nov 2011 22:56:04 GMT

"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);

Re: Outer Join then Replace Null Values

rwunderlich — Thu, 24 Nov 2011 00:31:57 GMT

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

Re: Outer Join then Replace Null Values

Anonymous — Tue, 17 Jul 2012 22:26:22 GMT

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

Re: Outer Join then Replace Null Values

rwunderlich — Wed, 18 Jul 2012 20:49:38 GMT

Before.

Re: Outer Join then Replace Null Values

pentaxadmin — Fri, 04 Aug 2017 20:00:19 GMT

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