14 Replies Latest reply: Nov 5, 2012 1:37 PM by Hiren Parikh RSS

    Removing Rows with Nulls

      Good afternoon, ive got a situation where I am getting nulls in some of my fields, I know why they are there and it doesnt matter. My Flat table data is a mix of Dimension data and Expression data. How do I go about excluding an entire row from my flat table if a field has a Null value in it please?

       

      Very Kind regards.

       

      Paul.

        • Removing Rows with Nulls
          Henric Cronström

          Load ... from ... where not IsNull(field) ;

          or

          Load ... from ... where Len(Trim(field))>0 ;

            • Removing Rows with Nulls

              Hi Henric, thanks for the response, the particular field that has the null value is not calculated in the script its an expression that loads all relative data including nulls. I want qlikview to not load rows where any row contains a null either in dimension or expression.

                • Removing Rows with Nulls
                  Henric Cronström

                  Not sure I understand...

                  If you do not want QlikVIew to load these records, then you should use a where clause in the script where the condition tests for NULL.

                  If you, on the other hand, want QlikView to load all records but just use some of the records for a specific calculation, then you can use a similar condition inside the calculation i.e. inside the aggregation function, e.g.

                     Sum( if(not IsNull(field), Amount) )

                     Sum( {$<field={"*"}>} Amount )

                   

                  HIC

                    • Removing Rows with Nulls

                      Hi Heric, Here is the script for the table I am using :-

                       

                      Data_table:

                      LOAD Internal_ID_For_Meter_Reading_Document,

                           Meter_Reading_Reason

                      FROM

                      **

                      where Meter_Reading_Reason='01'

                      or Meter_Reading_Reason='02'

                      ;

                       

                      I am using an expression which shows data in the same row which conforms to either one of the 'Meter reading' conditions above, but at the same time its pulling data that doesnt fit '01 or '02' hence the null.

                       

                      I want the script to not load the row at all if either '01' or '02' are not fullfilled. Trouble is if I use the And instead of 'OR' No data will load at all.

                • Removing Rows with Nulls

                  To suppress dimensions where all expressions result in NULL, go to the "Presentation" tab and check the box for "Suppress Missing".

                   

                  To suppress expressions where the dimension is NULL, go to the "Dimensions" and, for that dimension, and check the box for "Suppress When Value Is Null".

                   

                  To hide a dimension based on a more advanced criteria, such as a specific expression returning NULL (ignoring the results of the other expressions), create a new calculated dimension with that expression.  For that dimension, check the box for "Suppress When Value Is Null".  Finally, go to "Presentation", and, for that column, select the option for "Hide Column", which will prevent it from showing.  That allows you to have advanced control over the display of dimensions by hiding any rows where the dimension returns NULL, which can occur for any reason you want.