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:
ApplyMap('Item_Map', ItemNumber, ' ') as ItemDescription //forcing spaces when ItemDescription deosn't exist.
hope it helps,
Here's a generalized approach to converting nulls to blanks after join. First define a mapping table:
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;
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?
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!)
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?
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.