topic Re: Matching via null value in QlikView

Matching via null value

Fri, 06 Feb 2015 14:29:41 GMT

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_1	field_2	additional_field
1	2	abcd
1	3	efgh
2		ijkl
3		mnop

field_1	field_2
1	2
1	3
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.

Re: Matching via null value

sunny_talwar — Fri, 06 Feb 2015 14:36:55 GMT

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

Best,

Re: Matching via null value

giakoum — Fri, 06 Feb 2015 15:12:18 GMT

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;

Re: Matching via null value

giakoum — Fri, 06 Feb 2015 15:13:02 GMT

also see attached

Re: Matching via null value

daveamz — Fri, 06 Feb 2015 15:32:35 GMT

You must use a separator:

field1&'|'field2 as %KEY

field_1	field_2	additional_field	%KEY
1	2	abcd	1\|2
1	3	efgh	1\|3
2		ijkl	2\|
3		mnop	3\|

field_1	field_2	%KEY
1	2	1\|2
1	3	1\|3
2		2\|
3		3\|

Regards,

David

Re: Matching via null value

sunny_talwar — Fri, 06 Feb 2015 15:41:16 GMT

Works without the separator also. PFA

Best,

Re: Matching via null value

daveamz — Fri, 06 Feb 2015 15:54:47 GMT

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

sunny_talwar — Fri, 06 Feb 2015 16:01:01 GMT

Makes sense. Thanks for bringing that up.

Best,

field_1	field_2	%KEY
1	2	1\|2
1	3	1\|3
2		2\|
3		3\|