Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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,
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.
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
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
Thanks for your help Arnaldo, appreciate it.