Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Outer Join then Replace Null Values

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

1 Solution

Accepted Solutions
rwunderlich
Luminary Alumni
Luminary Alumni

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

View solution in original post

9 Replies
ToniKautto
Employee
Employee

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

Not applicable
Author

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

ToniKautto
Employee
Employee

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?

Not applicable
Author

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.

Not applicable
Author

"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
Luminary Alumni
Luminary Alumni

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

View solution in original post

ramya_beginner
Creator
Creator

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

rwunderlich
Luminary Alumni
Luminary Alumni

Before.

bujnakbranislav
Partner
Partner

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