Show results in straight table if a field in different columns is empty "NULL"

[#1]

Dear community,

I've been puzzled with (probably) a simple question.

As the title says, I cannot seem to get data in a straight table if one of the colums has an empty field or NULL.

In the example below I wanted to show all lines if one of the fields is empty (Email, Function or Phone Number).

So i was trying to use the IF function in the expression but I cannot seem to get it working. The formula should look like

IF(Email='nul', OR Function='null', Or Phonenumber='null' then show all records)

Can anyone please assist?

Regards,

Sijmen

[#2]

Noone knows? Or is is not possible in QV?

[ecolomer]

You can try change Null by another sign '-' par example in the script of charge

[#4]

Thanks for the reply Enrique, I see what you are saying, but how do I code the IF statement even if I change the sign '-' to something else?

[ecolomer]

Realy you want see or non see the lines with fields nulls?

[#6]

Yes, the query is used to check if our data is complete.

So basically. Show the lines with data is one of the three fields is empty or null. Then we can look up the datasource file and fill in the empty fields.

[#7]

This doesn't seem to be nulls. It seems to be empty spaces.

So you can simply do the following in your script.

if(Email = '', 'NULL', Email) as Email

if(Phonenumber = '', 'NULL', Phonenumber) as Phonenumber

if(Function = '', 'NULL', Function) as Function

Regards,
Marius

[#8]

And then you can do the following in your expression.

sum(if(Email = 'NULL' or Phonenumber = 'NULL' or Function = 'NULL', 1))

Regards,
Marius

[#9]

I have attached another solution where you can do this in the front end.

The object's name is 'Marius'.

Regards,
Marius

[tobias_klett]

Hi Sijmen,

load the fields in the script like this:

Tablexy:

Load

     if(isnull(Email),'null',Email)                               as Email,

     if(isnull(Function),'null',Function)                     as Function,

     if(isnull(Phonenumber),'null',Phonenumber)    as Phonenumber,

...

You could also do a flag for datasets with one null value like this

     if(isnull(Email) or isnull(Function) or isnull(Phonenumber),1,0) as Null_Flag,

Afterwards you can select the desired null-values or the Flag_Field and alternatively use Set Analysis to show the chart:

only( {<Null_Flag={1}>} FieldXY)

Hope this helps

Tobias