5 Replies Latest reply: Sep 26, 2012 7:12 AM by Miguel Angel Baeyens de Arce RSS

    Count Nulls

      Afternoon, I've done a quick search but can't quite find the answer I need.

      Basically I'm loading in data like below and beed to only show the rows where the date returned is blank, how do I write this as an expression.

      I don't think I can do this via the load script as I need to calculate other things.

       

      Can anyone help?

      Thanks

       

      DatePersonDateReturned
      01/01/2012J. Bloggs10/02/2012
      02/01/2012A.N Other
      03/01/2012C. Clott
      04/01/2012S. Else11/02/2012
        • Re: Count Nulls
          Miguel Angel Baeyens de Arce

          Hi,

           

          I'd do that in the load script, using the Len() function, it will perform faster and will make easier to count them in the expressions:

           

          Table:
          LOAD Person,
               Date,
               DateReturned,
               If(Len(DateReturned), 0, 1) AS NullCounter
          FROM ...
          

           

          The expression should be now as simple as:

           

          Sum(NullCounter)
          

           

          Or tu sum up the Sales field only for those who have nulls:

           

          Sum({< NullCounter = {1} >} Sales)
          

           

          But if you still want to do it in an expression, do something like the following:

           

          Only({< DateReturned = {"=Len(DateReturned) = 0"} >} Person)
          

           

          Hope that helps.

           

          Miguel

            • Re: Count Nulls

              that doesn't seem to be laoding correctly - this is what my script looks like

               

               

              SQL

               

              SELECT search_Incident_Custom_Joined.IncidentID,
              search_Incident_Injuries.DateReturnedToWork,
              search_Incident_Injuries.InvolvedPerson,
              If(Len(DateReturnedToWork),0,1) AS NullCounter

              FROM .......

               

               

                • Re: Count Nulls
                  Miguel Angel Baeyens de Arce

                  Hi,

                   

                  Use the LOAD always before the SQL statement:

                   

                  Table:

                  LOAD Field1,

                     field2

                  ;

                  SQL SELECT Field1, field2

                  FROM ...

                   

                  Hope that helps,

                   

                  Miguel

                    • Re: Count Nulls

                      sorry that is still not working, when I reload there is the following error "Incorrect syntax near the keyword 'If'."

                       

                      I've added the Load statement before the SQL, everything still works except for this line

                       

                       

                       

                      If(Len(DateReturnedToWork),0,1) AS NullCounter

                       

                       

                       

                      Have I missed something???

                        • Re: Count Nulls
                          Miguel Angel Baeyens de Arce

                          Hi Melanie,

                           

                          Actually yes. The IF() function in this case is a QlikView function, not a SQL function. Your script should look like this:

                           

                          // This part may change, and you may or may not not require the table name before the field name
                          Table:
                          LOAD IncidentID, 
                                    DateReturnedToWork,
                                    InvolvedPerson,
                                    If(Len(DateReturnedToWork), 0, 1) AS NullCounter
                          ;
                          SQL SELECT search_Incident_Custom_Joined.IncidentID, 
                                    search_Incident_Injuries.DateReturnedToWork,
                                    search_Incident_Injuries.InvolvedPerson
                          FROM .......
                          

                           

                          Hope that makes sense now.

                           

                          Miguel