How to find out when a DB_field is empty

[datanibbler]

Hi,

I have a problem loading data from a database - unfortunately it must remain somewhat theoretic because I have no direct access to that database, so I cannot see what the original data looks like. What I see in QlikView is this:

- An SQL SELECT produces, among others, two fields: Item_Nr and Date

     - The ITEM_Nr is just a number;

     - The DATE has the format 'DD.MM.YYYY hh:mm:ss TT';

=> I have the impression that, in contrast to our other databases where the Date_field is always populated, in that one there are a nr. of items with no entry in the date_field.

=> I found out by bringing up two lists for ITEM and DATE, selecting all values of DATE and then right-clicking on ITEM and saying "select excluded records"

   => that would return me all the ITEMs with no entry in the field DATE associated, yes?

- That, as I said, is in difference to the other (two) databases we have where every ITEM has a DATE associated.

=> That difference apparently is what leads to trouble lateron.

(this is just a trial in a somewhat mirky data_problem we have)

=> My question is: How can I (in the script) find out whether/ when that date_field is blank? I have already tried the ISNULL(), ISNUM() and ISTEXT() functions, but they don't work

Thanks a lot!

Best regards,

DataNibbler

[Nicole-Smith · Accepted solution]

if(len(trim(DateField))>0, 'Populated', 'Not Populated') as IsDate

[maxgro]

          len(trim(field))=0

[agilos_mla]

You can also activate some options within the QV script, when you read Database Tables.

NullAsValue *;          // either * for All or specific fieldnames separated by comma

Set NullValue = 'NULL';

Then NULL value will appear in Listbox also.

Michael

[datanibbler]

Hi Nicole,

that works like a charm! Finally!

Many thanks!

I wonder, however, why those others didn't work - strangely, they were altogether unavailable for all those ITEMs with no DATE ...

I guess the others would work, too, but yours was the first I tried.

Best regards,

DataNibbler