topic Re: using an if statement inside 'where' in QlikView

using an if statement inside 'where'

Mon, 11 Feb 2013 12:02:40 GMT

HI i need a little help here.

Basically I have a variable called owner and I want to use this inside a where clause, however I only want to include this if there is something inside the variable.

At the minute I am using this which is including it regardless of whether there is anything inside owner or not.

FROM REP_TASK_NF WHERE CANCELLED_DATE = '' AND OWNER = '$(owner)'

I need it to only use owner if it IS NOT NULL, how do i do this?

Thanks

Re: using an if statement inside 'where'

alexandros17 — Mon, 11 Feb 2013 12:20:14 GMT

You may check OWNER or $(owner) so if you add the condition "AND NOT OWNER is null" or if you are loading data with LOAD : "AND not isnull($(owner))"

let me know

Re: using an if statement inside 'where'

Mon, 11 Feb 2013 12:29:01 GMT

FROM REP_TASK_NF WHERE CANCELLED_DATE = '' AND OWNER = '$(owner)' AND NOT OWNER is null

Like this??

But wont that "AND NOT OWNER is null" check to see that the column/field OWNER is empty?? or am i misunderstanding what that does?

owner is essentially a text input which you either put something in or you dont. if its full then i need to include it in the where. if not i can miss the OWNER field out in the where

Re: using an if statement inside 'where'

flipside — Mon, 11 Feb 2013 12:31:52 GMT

Hi,

Try dynamic script for your where clause like this ...

let owner = 'A';

IF '$(owner)' = '' or IsNull($(owner)) then

set vWhere = CANCELLED_DATE = '' AND OWNER = '$(owner)';

ELSE

set vWhere = CANCELLED_DATE = '';

ENDIF;

... and call the where clause as ... resident Data where $(vWhere);

flipside

Re: using an if statement inside 'where'

alexandros17 — Mon, 11 Feb 2013 12:35:42 GMT

Ok, if you want to test the variable after the Select use a Load and add a where condition like this

Where not isnull($(owner))

Re: using an if statement inside 'where'

Mon, 11 Feb 2013 12:42:35 GMT

IF '$(owner)' = '' THEN

SET oWhere = AND OWNER = '$(owner)'

ELSE

SET oWhere = ''

ENDIF;

SQL SELECT CANCELLED_DATE,CLOSED_DATE,CLOSED_TIME,DEADLINE_DATE,DEADLINE_TIME,DOCUMENT_TYPE,TASK_FAULT_CODE_ID,TASK_ID,TASK_TYPE,X_OPEN,OWNER

FROM REP_TASK_NF

WHERE CANCELLED_DATE = ''

$(oWhere)

AND DOCUMENT_TYPE = '$(documentType)'

AND DEADLINE_DATE <> ''

AND DEADLINE_DATE >= '$(FromDate)';

I've used this and even though theres something in $(owner) i have no results 😕

Thanks

Re: using an if statement inside 'where'

flipside — Mon, 11 Feb 2013 13:02:41 GMT

Make sure you terminate the SET statements with a semi-colon as in my example earlier.

flipside

Re: using an if statement inside 'where'

flipside — Mon, 11 Feb 2013 13:08:46 GMT

Also helps if I give you the code the right way round ...

IF '$(owner)' = '' THEN

SET oWhere = '';

ELSE

SET oWhere = AND OWNER = '$(owner)';

ENDIF;

flipside

Re: using an if statement inside 'where'

Mon, 11 Feb 2013 13:09:24 GMT

Yea i just noticed those.

Thats working good now.

One more thing though, I have 2 fields like this. is there a way of concatinating these onto the end of the where if their is data in one of or both of these variables? instead of writing out the long where for each one.

Re: using an if statement inside 'where'

flipside — Mon, 11 Feb 2013 13:18:19 GMT

You could either build them into your IF statement or append field filters to your oWhere variable ...

IF '$(owner)' = '' THEN

SET oWhere = '';

ELSE

SET oWhere = AND OWNER = '$(owner)';

ENDIF;

IF '$(documentType)' = '' THEN

SET oWhere = '$(oWhere)';

ELSE

SET oWhere = $(oWhere) AND DOCUMENT_TYPE = '$(documentType)';

ENDIF;

flipside

Re: using an if statement inside 'where'

Mon, 11 Feb 2013 15:13:52 GMT

That all works ok. Thanks for your help.