Handling Nulls after concatenation

[#1]

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".

[alexandros17]

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

[Anonymous]

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

[kuba_michalik]

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.

[#5]

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.

[Anonymous]

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
];

[#7]

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.

[Anonymous · Accepted solution]

Hi Yoni,

Check below link, might get some idea:

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

[#9]

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..

[Anonymous]

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