15 Replies Latest reply: Feb 9, 2012 11:41 AM by Keith Cammans RSS

    How to select BLANKS or <NULL> values

    Kees den Boer


      I have created a tablebox which containes values like this:


      fielda fieldb fieldc fieldd fielde

      1 2 A B C
      3 4 D E F
      5 G H I
      6 7 J K

      etc, etc

      In fieldb and fieldc there a lot of records with no value. What I want is that the user can select those BLANKS so that only the recors appears which do not have a vlaue in column fieldb or fieldc.

      I have read this in other threads:


      Do you have a NullAsValue in your script ?

      or check your NullInterpreter variable ?


      However nothing works. Can anybody help me with this?

        • How to select BLANKS or <NULL> values

          Isnull : to select blanks

          NOT isnull : to select where not blank

          Is this along the right lines.........


            • How to select BLANKS or <NULL> values
              Kees den Boer

              I'm sorry I'm not sure what you mean by that . I don't think that NOT isnull will solve my problem, because I want to see everything that ISNULL.

                • How to select BLANKS or <NULL> values
                  Jay Edgson



                  Nul AsValue *;

                  In your script should work

                    • How to select BLANKS or <NULL> values
                      Kees den Boer



                      I've tried something like this, but the empty one still do not shows up. Not in Customer Number nor in Not Available.




                      CONNECT TO something;














                      CUSTNMBR as [Custumor Number]
                      IF("DOCNUMBR" <> '<NULL>', "CUSTNMBR", '000000000000') as [Not Available]; NullValue='<NULL>';
                      NULLASVALUE *;

                        • How to select BLANKS or <NULL> values
                          Jay Edgson

                          Put the NullAsValue at the beginning of the script

                            • How to select BLANKS or <NULL> values
                              Kees den Boer

                              Hi it still doesn't work. My very first line in the script is:

                              NULLASVALUE *;

                              Then I test DOCNUMBR with the following if statement.





                              (IsNull("DOCNUMBR"), 'Not Available', ' ') as [Not Available];

                              The result is in case that the DOCNUMBR contains a value, that [Not Available] will have the value ' ' as expected.
                              When DOCNUMBR doesn't has a value, the result is that [Not available] has a <NULL> value. I'm expecting there the text "Not Available".

                              When I select in the tablebox the '' value in the [Not Available] column all rows are selected with exeption of the rows with a <NULL> value as expected.
                              When I try to select the <NULL> value, nothing happens at all. It seems that QlikView can't make that selection.

                              So, I'm still confused. Any idea's?

                                • How to select BLANKS or <NULL> values
                                  Miguel Angel Baeyens de Arce


                                  You can flag those records and then select only them. RangeNullCount returns the number of null values in the passed expressions. You only have to pass on the function all fields, creating a new field in the LOAD statement:


                                  RangeNullCount(fielda, fieldb, fieldc, fieldd, fieldd) AS NullCount

                                  Then you can use any where statement, conditional expression or set analysis to select only those records where NullCount is greater than 0.

                                  Hope that helps!

                                  • How to select BLANKS or <NULL> values
                                    Jonathan Dienst

                                    I don't think NullAsValue helps the user to select null values. Essentially you cannot select null values using a click select. You can transform the null values as you have tried, although I would probably use something like this during loading

                                    If(IsNull(DOCNUMBR), 'Not Available', 'Available') as [IsAvailable],

                                    Alternatively, you can use Advanced Search to find the nulls. If you select a list box and start typing, the search dialog comes up. Normally, the search string starts with ** (wildcards), but if you select a list box and type =, you can enter an advanced search expression, for example:

                                    =IsNull(DOCNUMBR) or =Len(DOCNUMBR)=0 (these two are equivalent, in theory, but I find the second more reliable)

                                    That will select all entries in the list box that have (or are linked to other table records that have) a null value of DOCNUMBR.

                                    If that is too complicated for your users, you can save the advances serach in a bookmark.



                                      • How to select BLANKS or <NULL> values


                                        i think nullasvalue or isnull solutions doesn't work for your case.Because your values are blank values.

                                        You must try a solution like this.


                                          • How to select BLANKS or <NULL> values
                                            Kees den Boer

                                            Thank you all for trying to help me, but I have the idea that there is no solution for this. The Tablebox is a combination of three different tables where DOCNMBR is the keyvalue. What I try to select is a value that doesn't exist and that will not work at all ofcourse. So this is what is in my tablebox


                                            FROM TABLE A FROM TABLE B FROM TABLE C

                                            1 1 1
                                            2 2
                                            3 3 3
                                            4 4
                                            6 6 6

                                            So if I select the empty value in row 2 I select a value that doesn't exist, so now I understand that QlikView doesn't make a selection. So I'm affraid this will never work or does any one have an idea ?

                                              • How to select BLANKS or <NULL> values
                                                Jay Edgson

                                                In the load script, you could map the fields from Table B and Table C into Table A. Then in the apply map statement you could specify Not Available when there is no record in Table B/C.

                                                • How to select BLANKS or <NULL> values
                                                  Miguel Angel Baeyens de Arce

                                                  What you say is right: you cannot select a null value because it doesn't exist.

                                                  But, if I understood your question right, if you create a flag field to mark all records in the table that don't have any value will allow you to add this new field to your tablebox so you will be able now to select those records flagged (those who have one or more null fields).


                                                    • How to select BLANKS or <NULL> values
                                                      Kees den Boer

                                                      This is how I finally make it happens. I created the expression below where [Field Name] is the column name with the NON existing values.






                                                      if (isnull([Field Name]),1,0)


                                                      Thank you all for your help


                                                        • How to select BLANKS or <NULL> values

                                                          One more solution, if you ask yourself against what key the value is missing then you can make a listbox with a selection expression:    = if(nr<> '',  if(isnull(value), 'Missing', value), value) .

                                                          Basically check if the key exists and if not then check the missing value records.  Just checking the missing value records doesnt work.


                                                          the loadscript that belongs with this is



                                                          load * inline [

                                                          nr, letter

                                                          1, A

                                                          2, B

                                                          3, C

                                                          4, D

                                                          5, E

                                                          6, F ];




                                                          load * inline [

                                                          letter, value

                                                          A, 123


                                                          D, 235

                                                          F, 567 ];


                                                          A regular listbox on the value field will return nr 3, but not record nr 2 and 5 for which the letter is missing in the 2nd table.

                                                          The expression above will let you select those records with missing value in a listbox.


                                                          Another solution is of course to create a resident table which combines both and checks for missing values.