topic Re: Table Object "Omit Rows Where Field is NULL" in QlikView

Table Object "Omit Rows Where Field is NULL"

joshabbott — Thu, 24 Jul 2014 17:20:29 GMT

Hello,

I've got a problem in a bigger application and have created a small sample app to show where the problem is. The script in my sample that is attached is simple (See Below). The problem is there are 7 records. All records are in the fact table. There are 6 records in the Person table. The only id missing in the person table is 382. If I run the script, and add a table object to my application with both 'ID' and 'Person' as columns, all 7 row's are displayed. But if I go into the 'Presentation' tab of the table object, and for 'ID' set the 'Omit Rows Where Field is NULL' to true (check it), ID 382 disappears. This isn't making much sense to me since the list box of ID's doesn't show null as an available value from my fact table. Could someone please explain why it is doing this to me Thanks!

Fact:

LOAD * INLINE [
ID
376
377
378
379
380
381
382
];

Person:
LOAD * INLINE [
ID, Person
376, Not Provided
377, Not Provided
378, Not Provided
379, Not Provided
380, Not Provided
381, John Smith
];

Re: Table Object "Omit Rows Where Field is NULL"

Thu, 24 Jul 2014 17:40:30 GMT

Hi Josh,

This is typical Table box behaviour.

Here it checks all the fields and if there is a null value it applies a filter(excludes) on the whole table box display.

Even if you add another Table(see attached QVW) and apply "Omit Rows where Field is NULL" on any of the Table box fields, it would filter the entire table box content.

Hope it helps.

Thanks

Sabal

Re: Re: Table Object "Omit Rows Where Field is NULL"

Thu, 24 Jul 2014 17:50:40 GMT

Thats because you are asking it to suppress it since there is no value in Fact table. If you dont want it to be null, then you can concatenate another table with the missing results like in the attached file.

Thanks

Re: Table Object "Omit Rows Where Field is NULL"

maxgro — Thu, 24 Jul 2014 17:51:26 GMT

the listbox shows the ID you have in the field ID, some from Fact, some from Person

if you want to understand how Qlik handles null you can read

NULL handling in QlikView