topic Select a null value from a field with a VBscript Macro in QlikView

Select a null value from a field with a VBscript Macro

Fri, 24 Jul 2009 19:50:38 GMT

Hi,

I'm desperately trying to select null values from a field.
I need to make null selections of a field from a button.

Is it possible or not ?

That's the vbscript

Sub Test
ActiveDocument.Fields("Start_Date").Select "isnull()"
End Sub

Sub Test_two
set f = ActiveDocument.GetField ("Start_Date")
f.ParetoSelect "isnull(Start_Date)"
End Sub

Thanks in advance for your answers, so far so fast so good...

russianblue

Select a null value from a field with a VBscript Macro

Oleg_Troyansky — Fri, 24 Jul 2009 20:19:24 GMT

I don't think you can select a null value - not from a macro, and not manually on the screen. You'd need to modify your script and replace nulls with some kind of value, like "N/A" or "MISSING" or something like that.

Oleg

Select a null value from a field with a VBscript Macro

Fri, 24 Jul 2009 21:53:10 GMT

Thanks Oleg,

I already applyed the solution you suggested me in other reports I've made.

I just thought if there was a different solution using a macro instead of modifying the script...

Select a null value from a field with a VBscript Macro

Fri, 24 Jul 2009 22:51:01 GMT

Do your Null values show up in the Field Selection Boxes as blanks?

I just did a quick test with Inlined data (containing some nulls). In the macro, I used:

ActiveDocument.Fields("Value").Select ""

And it selected the Null (blank) value. I'm not sure if this will work for all situations.

Select a null value from a field with a VBscript Macro

Wed, 29 Jul 2009 20:45:26 GMT

I tried to select null values using:
ActiveDocument.Fields("My Field").Select ""
but it doesn't work...

at least I had to modify the script using an "NA" value...

thanks

Select a null value from a field with a VBscript Macro

Anonymous — Thu, 30 Jul 2009 10:30:49 GMT

The blank values from an inline load are not null values but blank values and will not respond to isnull() for example. If you try loading from Excel with blank cells you will get proper nulls.

If you do need null values and want to be able to interact with them you should look up NullAsValue in the F1-help in QlikView for null handling.

Select a null value from a field with a VBscript Macro

Thu, 30 Jul 2009 14:20:51 GMT

I already took a look to "NullAsValue" statement and found that it's a dangerous solution... sometimes it can display unespected join results. As I said I'm just looking for an easy way to select in a Field null values with a macro. Thank you so much for your interest. rb

Select a null value from a field with a VBscript Macro

Thu, 30 Jul 2009 18:20:24 GMT

Try thinking about it this way... QlikView data tables are not the same as, say, MSAccess tables. The QlikView data table is not a grid in which values are held, it is a collection of fields (columns) in which the values are associated with values in other fields (rows). Where there is no association, the grid DISPLAYED in a table object shows a null value. This is because the value does not exist, not that there is a value that is undefined.

I think what you are trying to do is to find something that is not there based on the assumption that QlikView has some way of knowing it is not there.

I have found that it is nearly always better to set a missing value as something known than to leave them as missing (as suggested by Oleg).