5 Replies Latest reply: Nov 21, 2016 2:22 PM by Richard Judkins RSS

    Exclude when column is blank

    Richard Judkins

      Hey all

       

      I have no doubt this is very simple, but...

       

      I'm trying to modify a pretty complex, existing QlikSense app, but have only been working with QlikSense for 3 weeks.

       

      Basically, an extremely large table with a large number of columns and rows.

       

      Part of the SQL is (this has been highly abridged, but to give you an idea):

       

      SELECT

            ,[CLIENT_ID]

            ,[PROD_NAME]

            ,[PROD_LN_NAME]

             ,case when year(CONVERT(DATE,TRANS_DATE,103))=2016 then CONVERT(DATE,TRANS_DATE,103)

         when year(CONVERT(DATE,TRANS_DATE,103))=2015 then DATEADD (dd , 364 , CONVERT(DATE,TRANS_DATE,103) )

         when year(CONVERT(DATE,TRANS_DATE,103))=2014 then DATEADD (dd , 728 , CONVERT(DATE,TRANS_DATE,103) )

         END AS REFERENCE_DATE

         ,CONVERT(DATE,TRANS_DATE,103) as [TRANS_DATE]

            ,[TRANS_DATE_EQUAL_REG_DATE]

            ,[PROD_GROUP]

            ,SALE_ASSET_VERSION

            ,PRIMARY_AGREE_ITEM_FLG

            ,case when PROD_GROUP like '%Widget%' then 'Blue Widget'

         when PROD_GROUP = 'Flapjacks' then 'Pancakes'

      else PROD_GROUP end as PROD_CATEGORY

      case when [LEGAL_ENTITY] like '%AA%' then 'AB' else 'AC' end as Country

         ,CASE WHEN PROD_LN_NAME = 'Squids' THEN 'Squids' ELSE

             CASE WHEN CATEGORY = 'Playing' OR CATEGORY = 'Sleeping' THEN ‘Breathing' ELSE

             'Dead' END

              END AS REFERRAL_TYPE

       

      What I want to do is for it to ignore any rows where the CLIENT_ID is blank.

       

      I've tried WHERE [CLIENT_ID] <> NULL, and WHERE [CLIENT_ID] IS NOT NULL, etc etc, but because of the multitude of existing CASE and JOIN statements etc I can neither the right place to enter it or the right syntax to use.

       

      Any help greatly appreciated!

        • Re: Exclude when column is blank
          Arnaldo Sandoval

          Hi Richard,

           

          It could be that CLIENT_ID is never null at all, perhaps it contains spaces or its length is zero, I will suggest to filter the data as follow:

           

          WHERE [CLIENT_ID] IS NOT NULL AND LEN( TRIM ( [CLIENT_ID] ) ) > 0

           

          The expression LEN( TRIM ( [CLIENT_ID] ) ) returns a positive value when [CLIENT_ID] is not null and contains some information, the TRIM() function removed leading and trailing spaces, and LEN() returns the length of whatever is left.

           

          Hope this helps,

            • Re: Exclude when column is blank
              Richard Judkins

              Thanks for the prompt reply, Arnaldo!

               

              My apologies, I should have been more specific.

               

              It's not that it's not working insofar that it's still pulling through blanks records - it's actually failing to execute at all, wherever and however I try to enter the script.

              I get the red "error" message - either "incorrect syntax near WHERE" or "incorrect syntax near ','" or near "FROM" or near anything at all.

                • Re: Exclude when column is blank
                  Arnaldo Sandoval

                  Richard,

                   

                  If that is the case, then you have a syntax error in your select statement, difficult to find as the previous developer did not indent the query, I applied some indentation, and you will notice where the code is missing a comma.

                   

                  SELECT

                        , [CLIENT_ID]

                        , [PROD_NAME]

                        , [PROD_LN_NAME]

                        , CASE

                              WHEN YEAR(CONVERT(DATE,TRANS_DATE,103))=2016 THEN CONVERT(DATE,TRANS_DATE,103)

                              WHEN YEAR(CONVERT(DATE,TRANS_DATE,103))=2015 THEN DATEADD (dd , 364 , CONVERT(DATE,TRANS_DATE,103) )

                              WHEN YEAR(CONVERT(DATE,TRANS_DATE,103))=2014 THEN DATEADD (dd , 728 , CONVERT(DATE,TRANS_DATE,103) )

                          END                                              AS REFERENCE_DATE

                        , CONVERT(DATE,TRANS_DATE,103)                     AS [TRANS_DATE]

                        , [TRANS_DATE_EQUAL_REG_DATE]

                        , [PROD_GROUP]

                        , SALE_ASSET_VERSION

                        , PRIMARY_AGREE_ITEM_FLG

                        , CASE

                              WHEN PROD_GROUP LIKE '%Widget%' THEN 'Blue Widget'

                              WHEN PROD_GROUP = 'Flapjacks'  THEN 'Pancakes'

                              ELSE PROD_GROUP

                          END                                              AS PROD_CATEGORY

                          CASE /* you are missing a comma on this case statement */

                              WHEN [LEGAL_ENTITY] LIKE '%AA%' THEN 'AB'

                              ELSE 'AC'

                          END                                              AS Country

                        , CASE

                              WHEN PROD_LN_NAME = 'Squids' THEN 'Squids'

                              ELSE

                                    CASE

                                        WHEN CATEGORY = 'Playing' OR CATEGORY = 'Sleeping' THEN ‘Breathing'

                                        ELSE 'Dead'

                                    END

                          END                                              AS REFERRAL_TYPE

                   

                  You are missing a comma at your third CASE or it went missing when you copy and pasted you query.

                   

                  hth