Searching on missing values in a table

[chriscools]

Hello,

we are preparing a datamigration for the step to another ERP-system.

I'm using qlikview as an analyzing tool for dataquality before we start uploading.

now i have the problem i have to find a way to search for colums with empty fields.

(missing adress, missing VAT, ...)

now for example:

in my table i have 3000 rows, and in 1 column there are 5 possible values: A/B/C/D/E

but in reality only 2900 rows are filled for this column, in the other it shows : "-"

when i create a "multiple choice list" i can only choose values A/B/C/D/E,

but what i want is to be able to search so i get only the rows where no value is entered and that i can distribute a list

of only those rows to fill with the value.

is this possible and what would be the way to do it?

thanx for your help!

grtz,

chris

[swuehl]

Chris,

if you do have a unique ID per record in your table, you can create a straight table with this unique field as dimension

and as expression

=if( isnull(YOURFIELDWITHNULLS), 1,0)

and then you can export this table to excel if you want (and you can also add more fields from that table as dimensions if you want to show more details).

Hope this helps,

Stefan

[chriscools]

Hey Stefan,

this could be a solution, but the problem is i have about 20 templates with each between 20 and 40 colums.

and for almost all of them i would want to search the rows with empty fields...

i think that this would be too much work.

i was hoping for a more easy way with less work! 🙂

thanx!

grtz,

chris

[swuehl · Accepted solution]

But you know the field(s) that you want to filter for NULL? Or do you need to check every field in every table?

You could also try to select all values in the field (using context menu in the list box for that field, select all values).

Then open the context menu on the list box of the unique id field and select excluded.

You can also work out a solution by creating a flag field per table in the script, but I don't know if this is any easier or with less work.

Or try using NullAsValue statement and NullValue variable in the script.

[chriscools]

Hello,

i used null as value and that works, i'm able to select the empty values!

the only problem now is that i had calculated % on the fill rate of each field,

and suddenly they were all 100% now... 🙂

so i removed the null as value again and i will work with the select excluded on the unique id field.

thanx!!!

grtz,

chris