Removing and reordering values in a filter

Hi, is there a way to remove NULL from this filter list and reorder it so <18 is at the top, followed by 18-44, 45-54 etc?

image

Thanks.

1 Like

Hi,

You can remove by the edit filter as image given below. You can apply any condition as you required.

image

Regards,

Naveed

1 Like

Thanks. Is there a way to reorder the list so “<18” is the first option, and change the word “null” to missing?

There is currently no way to order filters besides them being alphabetically.

In regards to the NULL, do you have the exclude Null option selected?

image

Hi Max, I actually think rather than excluding nulls, I would like to rename null to “missing”. Is it possible to do that?

you can write an ifelse statement.

ifelse(isNull({field},‘missing’,{field})

1 Like

I think I’m doing something wrong with the script.
I have a filter where I needed to change the fields from 1,2,9, Null, to yes, no, unknown, missing. My script has worked for yes, no, unknown, but I am still getting Null instead of missing.
I have tried various combinations:

image

image

image

image

None of them are working.

Any suggestions?
Thanks

@jotg - What is the datatype of 422TransferFromaExtHospital column? If it’s a string datatype then you can use something like :

ifelse(
coalesce(422TransferFromaExtHospital, ’ ') = ‘1’, ‘Yes’,
coalesce(422TransferFromaExtHospital, ’ ') = ‘2’, ‘No’,
coalesce(422TransferFromaExtHospital, ’ ') = ‘9’, ‘Unknown’,
coalesce(422TransferFromaExtHospital, ’ ') = ’ ', ‘Missing’, ‘’
)

In case its a integer datatype, something like below can be used :

ifelse(
coalesce(422TransferFromaExtHospital, 0) = 1, ‘Yes’,
coalesce(422TransferFromaExtHospital, 0) = 2, ‘No’,
coalesce(422TransferFromaExtHospital, 0) = 9, ‘Unknown’,
coalesce(422TransferFromaExtHospital, 0) = 0, ‘Missing’, ‘’
)

Let me know if this helps!

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!

2 Likes

@sagmukhe

The datatype is string.

I used the first script you suggested and it worked.

Thank you!

1 Like

@sagmukhe

I have tried to use a similar script to recode another of my data points (which I want to use as a filter).

But when I then use this new calculated field for a filter, I only have the following options

image

The gender field is a string, and I have successfully used this script for other datapoints/filters, with no issues.

What am I doing wrong with this one?

Thank you.

@jotg - If you see your expression you are comparing replacing the NULL value in each of the section with empty string; but in none of the condition it is being compared with empty string. Hence whenever the 213Gender field has null value this expression is returning empty string as defined by you in the else clause (as none of the condition is matching which you are comparing with values like Male, Female, Not stated, Space etc.). Hence you need to redefine this logic as per your requirement. Hope this helps!

1 Like

Hi @sagmukhe, I used the same script (except replacing the variable name) for this one:

And the filter looks like this (which is exactly what I wanted):

image

And I have tried it with having a space between the apostrophes in each line i.e. :

But this didn’t work either.
I just don’t know why it worked for type of stroke, but not gender.

Thanks for your help.

@jotg - Thanks for sharing the details. Did you check the content of your 213Gender column? If most of the values are NULL then it will go to the Missing category. Also, please check if there are leading or trailing spaces around the 213Gender column content. If that’s there then you need to trim those as well before comparing. Let me know. Thank you!

1 Like

@sagmukhe In the primary database, a record can’t be created without the gender field being entered, so the vast majority will not be NULL.

I don’t expect there to be leading or trailing spaces, as again, we pull this data directly from our web based data collection source)

Why this isn’t working is a real mystery to me.

Thank you.