Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
datanibbler
Esteemed Contributor

How to find out when a DB_field is empty


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

1 Solution

Accepted Solutions

Re: How to find out when a DB_field is empty

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

4 Replies

Re: How to find out when a DB_field is empty

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

MVP
MVP

Re: How to find out when a DB_field is empty

          len(trim(field))=0

agilos_mla
Contributor III

Re: How to find out when a DB_field is empty

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
Esteemed Contributor

Re: How to find out when a DB_field is empty

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

Community Browser