Exclude when column is blank

[Anonymous]

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!

[ArnadoSandoval]

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,

[Anonymous]

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.

[ArnadoSandoval]

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

[ArnadoSandoval]

Richard,

If you look at the query I posted, you will notice that the single quotes surrounding Breathing are incorrect, it is easier to notice in my post as I used the courier font.

hth

[Anonymous]

Thanks for your help Arnaldo, appreciate it.