Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

using an if statement inside 'where'

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

1 Solution

Accepted Solutions
flipside
Valued Contributor II

Re: using an if statement inside 'where'

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

10 Replies

Re: using an if statement inside 'where'

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

Not applicable

Re: using an if statement inside 'where'

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

flipside
Valued Contributor II

Re: using an if statement inside 'where'

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'

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))

Not applicable

Re: using an if statement inside 'where'

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

flipside
Valued Contributor II

Re: using an if statement inside 'where'

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

flipside

flipside
Valued Contributor II

Re: using an if statement inside 'where'

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

Not applicable

Re: using an if statement inside 'where'

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.

flipside
Valued Contributor II

Re: using an if statement inside 'where'

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