Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Matching via null value

Hi everyone,

I am importing some data into my qlikview document from a database and I am also importing an excel sheet to associate the database info to another field. The table created from the database joins with the excel table via 2 fields - I'll call them field_1 and field_2. All rows in my database table contain a field_1 value, but only some of them contain a field_2 value. For those rows that do contain a field_2 value it is necessary that I join to the excel table via both fields. For the rows that do not contain a field_2 value my joining isn't working even though there is a null in the database table and a null in the excel table for field_2. Is there something that I am not doing right?

field_1field_2additional_field
12abcd
13efgh
2ijkl
3mnop
field_1field_2
12
13
2
3

The table on the top is what my excel table looks like, and the one on the bottom represents the matching fields in the database table.

Thanks.

7 Replies

Re: Matching via null value

Try using a concatenated field (field_1 & field_2 as Join) to join your QlikView table with the excel file.

Best,

S

giakoum
Honored Contributor II

Re: Matching via null value

null is... null, it cannot be joined upon.

Replace null with something else to make it work:

NullAsValue

The NullAsValue statement specifies for which fields the encountered NULLs should be converted to values.

By default, QlikView considers NULL values to be missing or undefined entities. However, certain database contexts imply that NULL values are to be considered as special values rather than simply missing values. The fact that NULL values are normally not allowed to link to other NULL values can be suspended by means of the NullAsValue statement.

The NullAsValue statement operates as a switch and will operate on subsequent loading statements. It can be switched off again by means of the NullAsNull statement.

The NullAsValue can be combined with the variable NullValue that specifies which string to use as NULL value. If the variable NullValue is not used, NullAsValue will replace NULLs with empty strings.

The syntax is:

NullAsValue*fieldlist

*fieldlist is a comma separated list of the fields for which NullAsValue should be turned on. 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.

Example:

NullAsValue A,B;

Set NullValue = 'NULL' ;

Load A,B from x.csv;

giakoum
Honored Contributor II

Re: Matching via null value

also see attached

daveamz01
Contributor III

Re: Matching via null value

You must use a separator:

field1&'|'field2 as %KEY

field_1field_2additional_field%KEY
12abcd1|2
13efgh1|3
2ijkl2|
3mnop3|

field_1field_2%KEY
121|2
131|3
22|
33|

Regards,

David

Re: Matching via null value

Works without the separator also. PFA

Best,

S

daveamz01
Contributor III

Re: Matching via null value

Yes, in this case but if you have:

Field1     Field2,

1          11

11        1

1|11 <> 11|1

without separator you will have 111 = 111

David

Re: Matching via null value

Makes sense. Thanks for bringing that up.

Best,

S

Community Browser