Set Variable to Text Value with an if(then) statement

[leale1997]

New to qlik so this is probably a simple fix but I am just missing the solution. 

I have a data set with a field called date_closed. 

I want to create a variable that will display the text 'Open' anytime that field is null, and 'Closed' if the field is not null.

This is the script I have been trying to use:

SET vRecordStatus =IF(IsNull(date_closed), 'Open', 'Closed') ;

[Anonymous · Accepted solution]

Hi Erka,

The expression is fine. Just that use LET in place of SET. Your expression would be

LET vRecordStatus =IF(IsNull(date_closed), 'Open', 'Closed') ;

Cheers,

Ritesh Pathak

[leale1997]

Thanks so much!  That worked.

Can I bother you with one more question?  I want to assign the variable to an input box so that a drop down will appear, allow the user to choose between 'Open' and 'Closed' and change all the records on the page to correspond to the variable selection.

I created an input box and assigned the variable I created.  But only the 'Open' is appearing with no option to choose 'Closed'

[Anonymous]

Hi Erka,

Please goto variable overview and delete your variable vRecordStatus and then use below expression for your variable creation

LET vRecordStatus = if(IsNull(date_closed) or Trim(date_closed) ='', 'Open','Close') ;

it should work.

Let me know if you face any more issue.

Cheers,
Ritesh Pathak

[Anonymous]

Hi Erka,

What you could do is:

While loading the table itself load additional column in your script. See script below for details:

Load

Date_Closed,

if(IsNull(date_closed) or Trim(date_closed) ='', 'Open','Closed') as Status

From ...... ;

and use the new field 'Status' as a list box for filter and in your expression for chart calculation. Hope it should work.

Please confirm whether it worked or not.

Thanks and regards,

Ritesh Pathak