How do I remove NULLs after a LEFT JOIN LOAD so I can select blank fields?

[#1]

I have a bigtable and a smalltable, with a common key field. The smalltable does not contain records for all records in the bigtable. If I LOAD (main fields) from bigtable then LEFT JOIN LOAD (extra fields) from smalltable, I get NULL values in an extra field where a record did not exist in smalltable. If I try to select blank values in an extra field in a list box, I want to include NULL values and existing blank values. Is there a simple way of doing this?

[Oleg_Troyansky]

Well, that depends on your definition of "Simple"...

You can replace your LEFT JOIN logic with mapping and force blanks (or any default text) when the mapping is not available. For example, if your "big" table contains Sales and your "small" table contains Items, you can do something like this:

Item_Map:

mapping load

ItemNumber,

ItemDescription

resident Items;

Sales:

load

...,

ItemNumber,

ApplyMap('Item_Map', ItemNumber, ' ') as ItemDescription //forcing spaces when ItemDescription deosn't exist.

...

hope it helps,

Oleg

[rwunderlich]

Here's a generalized approach to converting nulls to blanks after join. First define a mapping table:

NullMap:
MAPPING LOAD
null(), ''
AUTOGENERATE 1
;

Then assume a table "TabA" with two fields, "Stock" and "Held", that you want to change nulls to blanks.

MAP Stock, Held USING NullMap;
RIGHT JOIN (TabA) LOAD DISTINCT * RESIDENT TabA;

-Rob

[#4]

Thanks Oleg... I tried the apply mapping, but to extend your example, I have an Orders table, a Lines table and a Products table. This means I need a mapping for each table link, and this starts to look untidy.

I like Robs answer, as I can join all the Tables, then map the NULLs away in one go.

But (is there always a 'but...'?)

I am loading my data from a SQL Server database using OLEDB. The fields are filled with spaces, so now I have to use the NULL mapping to the correct number of spaces. I tried the Verbatim variable, which is supposed to affect space-filling on fields, but setting it to 1, 0 or -1 made no difference (1 is supposed to turn off space-stripping, it should be on by default). I don't want to use ltrim( ) as it might make things slower.

Is something affecting the way Verbatim works?

[rwunderlich]

I'm unclear as to which fields are getting trimmed. I'm able to put multiple blanks in the mapping table and the blanks are retained. Is it the DB blanks that are getting stripped and you want to retain them?

-Rob

[#6]

It is the fields that are read from the SQL database that are filled with spaces; I have one field that is 10 spaces and one that is 8. I have to use two separate mapping tables for these fields, one to replace the NULL with 10 spaces and one for 8.

If I understand the (brief) notes I have found in the help for the Verbatim variable, both fields should be saved in the QlikView document with the spaces stripped as the default action, which is what I want and I could use one mapping table. I don't want to retain the spaces. If I use an expression len(fieldname) in a straight table, I can see the blank fields and how many spaces they contain.

It seems that the default Verbatim is not working as given in the help. (sorry, I hope I am not creating a new topic in this thread!)

[rwunderlich]

Verbatim won't trim an all spaces field down to '' (zero length). I don't see anything in the doc, but that's the way it appears to work. I think you'll have to use trim() when you read it from the DB.

-Rob

[#8]

Take a look in the documentation at the LEFT KEEP JOIN (page 476, in the 8.5 manual). The "KEEP" will keep only those records for which bigtable and smalltable (in your example) have a common key field... exactly what you wanted.

-A

[#9]

Sorry Anthony, I think you have misunderstood, I don't want to reduce the data, I want to expand it.

The solutions from Oleg and Rob will work, but a lot depends on the data and conditions attached to the data load. The biggest problem here is that QlikView will remove trailing spaces if there is data in a field, so "aa " is stored as a 2 character string and "bbbb " is stored as a 4 character string, but " " is stored as a 8 character string. Using either solution means having different length replacement strings, or using trim( ) on the load fields.

I am still working through a few examples (still new to all this), but I could document this in the wiki or create a discussion thread.

[Jason_Michaelides]

Hi Rob,

This certainly works but is really slow! To load (with some transformations) 25m records takes about 1.5 minutes and the LEFT JOIN that produces the NULL values takes another 30 seconds, but the NULL replacement method using the NullMap above adds an extra 5 minutes. I don't understand this as once the initial load is complete it should be optimized (I would have thought)!

Any way that you know of to replace nulls during the LEFT JOIN?

Jason