topic Re: Handling Nulls after concatenation in QlikView

Handling Nulls after concatenation

Mon, 26 Jan 2015 09:07:57 GMT

Hi,

I'm dealing with 2 fairly similar tables, although not identical with regard to column names (most of the columns are present in both tables). In the end, I'm concatenating the 2 tables to create a 'master' data table, on which my analysis will be made. The problem is with the not-in-common columns, which appear on one of the original tables but not on the other. By default, after concatenation the values there will be NULL. Is there a way to autofill these NULL columns with a value ("Unknown" will do)?

For example:

Table 1:

Vehicle	A	B
1	value1	value2

Table 2:

Vehicle	A	C
2	value3	value4

After concatenating the 2 tables, I'll get:

Vehicle	A	B	C
1	value1	value2
2	value3		value4

With NULLS in some cells on columns B & C. I want to autofill these cells with "unknown".

Re: Handling Nulls after concatenation

alexandros17 — Mon, 26 Jan 2015 09:18:51 GMT

Here it is the script example.

Temp:

Load ... resident TabA;

concatenate

Load ... resident TabB;

MyFinalTab

Load Vehicle, If(IsNull(A),'Undefined', A) as A, If(IsNull(B),'Undefined', B) as B, If(IsNull(C),'Undefined', C) as C resident Temp;

drop table Temp;

let me know

Re: Handling Nulls after concatenation

Anonymous — Mon, 26 Jan 2015 09:26:52 GMT

Hi,

Try:

Table:
load Vehicle,A,
IF(Len(Trim(B))= 0,'Unknown',B) as B,
IF(Len(Trim(B))= 0,'Unknown',C) as C;
LOAD * Inline
[
Vehicle,A,B,C
1,value1,value2,,
2,value3,,value4
];

Regards

Neetha

Re: Handling Nulls after concatenation

kuba_michalik — Mon, 26 Jan 2015 09:36:53 GMT

More or less, but you have to be tricky about it, if you want to avoid running a second pass over previously loaded data (it would make sense to avoid it if you have a ton of data and want to save on reload time, if you don't care, the solution similar to one by Alessandro would do fine).

There is a script option, NULLASVALUE, which replaces nulls with something else for subsequently loaded data. Hovewer, if you use Concatenate, it will do nothing for "missing fields" in the table you are concatenating to, because those rows are already loaded. So, the idea is to first use:

NULLASVALUE <your list of fields, can be * for all fields>;

SET NullValue = 'Unknown';

and then always be concatenating to a table that has all the fields (and preferably no rows at first, so you don't get any dummy data in your real data). You can create such a table stub either manually:

LOAD

'' as Vehicle, '' as A, '' as B, '' as C

Autogenerate(1)

Where False();

or get more creative by pulling the list of columns from your data sources (for example by loading 0 rows from each and concatenating the results )

If you don't care about the second pass, just set the same options (nullasnull, set nullvalue) and then do a reload from Resident result of all concatenations. Don't forget to add NoConcatenate for this one, or it will autoconcatenate to the original.

Re: Handling Nulls after concatenation

Mon, 26 Jan 2015 09:37:06 GMT

Hi,

Thanks to both of you, I know that these methods will work. However, I'm dealing with a fair amount of columns which are unique to one of each table, so defining this rule for each column name seems a bit cumbersome. I was wondering if there was a way to do this automatically to all cases with NULLs, without having to specify the column name.

Re: Handling Nulls after concatenation

Anonymous — Mon, 26 Jan 2015 09:41:20 GMT

Rectified Error
Table:
load Vehicle,A,
IF(Len(Trim(B))= 0,'Unknown',B) as B,
IF(Len(Trim(C))= 0,'Unknown',C) as C;
LOAD * Inline
[
Vehicle,A,B,C
1,value1,value2,,
2,value3,,value4
];

Re: Handling Nulls after concatenation

Mon, 26 Jan 2015 09:49:33 GMT

Hi Yoni,

have a look at the 'map using' function, this will allow you to handle multiple fields like this.

below is form the QV manual

hope that helps

Joe

Map ... using

The map ... using statement is used for mapping a certain field value or expression to the values of a specific

mapping table. The mapping table is created through the Mapping (page 265) statement.

The automatic mapping is done for fields loaded after the map ... using statement until the end of the script

or until an unmap statement is encountered.

The mapping is done last in the chain of events leading up to the field being stored in the internal table in

QlikView. This means that mapping is not done every time a field name is encountered as part of an expression,

but rather when the value is stored under the field name in the internal table. If mapping on the expression

level is required, the Applymap() function has to be used instead.

Note!

The map ... using statement does not work when loading QVD files in optimized Mode.

The syntax is:

map *fieldlist using mapname

*fieldlist is a comma separated list of the fields that should be mapped from this point in the script. Using *

as field list indicates all fields. The wildcard characters * and ? are allowed in field names. Quoting of field

names may be necessary when wildcards are used.

mapname is the name of a mapping table previously read in a mapping load or mapping select statement.

Examples:

map Country using

Cmap;

Enables mapping of field Country using map Cmap.

QlikView 11.20 SR5, 12235 265

24 Script Syntax

map A, B, C using X; Enables mapping of fields A, B and C using map X.

map * using GenMap; Enables mapping of all fields using GenMap.

Re: Handling Nulls after concatenation

Anonymous — Mon, 26 Jan 2015 09:56:10 GMT

Hi Yoni,

Check below link, might get some idea:

http://community.qlik.com/message/235181#235181

Re: Handling Nulls after concatenation

Mon, 26 Jan 2015 10:46:31 GMT

Neetha & Joe, thanks a lot! The combination of your methods was exactly what I needed. I actually used a similar method before when I had a problem with Joins, I didn't think the same solution would apply to concatenations as well..

Re: Handling Nulls after concatenation

Anonymous — Mon, 26 Jan 2015 10:51:50 GMT

Hi Yoni,

It's My Pleasure, I am Glad you are able to solve the issue.

Please can you copy paste solution here, so any one with same requirement can

easily solve it.

Regards

Neetha

Re: Handling Nulls after concatenation

Mon, 26 Jan 2015 12:36:12 GMT

Sure thing, here is the method:

Re: How to fill in blank or null values after outer join

jason.michaelides Jul 6, 2012 4:15 AM (in response to Francois de Wet)

I don't like having any NULLs in my data model as they are difficult to work with and not selectable. I normally replace NULLs with '<Unknown>' or something similar.

MAP...USING works well:

Map_Null_Unknown:

MAPPING LOAD

Null()

,'<Unknown>'

Autogenerate 1;

MAP Field1,Field2,Field3,etc USING Map_Null_Unknown;

Now all nulls listed in the MAP...USING statement will be replaced as they are loaded. However, nulls created by LEFT/OUTER joins will not be replaced, unless that field is subsequently loaded later in the script. If your joined fields with nulls are not loaded again (by some kind of resident load) then you will need to force it. This works well:

RIGHT JOIN (Table) LOAD DISTINCT * RESIDENT Table;

Re: Handling Nulls after concatenation

Mon, 26 Jan 2015 12:44:52 GMT

no worries, glad to help

Re: Handling Nulls after concatenation

mmarchese — Fri, 06 Mar 2020 21:02:26 GMT

Wouldn't this work and be the simplest? Why bother loading the data a second time just to replace the nulls?

LOAD Vehicle, A, B, 'Unknown' AS C FROM Table1; LOAD Vehicle, A, 'Unknown' AS B, C FROM Table2;