Hello! I'm trying to figure out if it's possible to filter a given field based on the character length of its value. This is for a clean-up project. In my case, I'm trying to find records where the "Matter Name" field is below a certain number of characters.
FYI - I do not have access to the backend configuration stuff for the e-cube, so any solution that involves messing with the tables or creating new columns will not be of help to me. I can only do what's available in the front-end UI. Also worth noting: our instance of Sisense is an older Windows version, v8.2.1. So make sure any solution you suggest will work on an older version like that. If it's something that would require us to update our Sisense then I'm out of luck 😞
I was hoping I could use underscores as a single-character wildcard, and then just chain a number of those together with an "equals" operator. However, this doesn't seem to be working as intended. For instance, if I say "equals ___" (three underscores), then it tells me "No Results", even though I know for a fact that some Matter Names are, in fact, exactly three characters long.
It seems like this is an issue with the "equals" operator. If I use "contains" instead, then it seems to correctly interpret the underscores, but of course this isn't very useful since doing it that way will ALSO pull in a lot of undesired results.
Interestingly, I was able to use this method to identify the opposite: Matter Names that are too long. Our character limit for the field is 250 characters, so saying "contains..." followed by 250 underscores will work just fine. However, if you change it from "contains" to "equals", it immediately says "No Results", even though the prior "contains" logic pulled up many 250-character names without issue. *shrug*
How can I filter down to names with a specific number of characters, or less than/more than a certain number of characters? Alternatively, if there's another way to do this outside of the filter UI, perhaps with a widget script, let me know. Thanks!
(NOTE: I'm aware that, as a workaround, you could simply export a pivot or table widget, use the LEN formula in Excel, then filter based on that. However, I'd like to find a solution that works within Sisense and doesn't require external steps to get the results you need.)
1 ACCEPTED SOLUTION
Okay, nevermind, figured it out on my own after more experimentation 🙂
For anyone else trying to do this: use a "Doesn't Contain" filter, and then chain the number of underscores for the number of characters you're looking for. This will show you with everything that has LESS than the number of underscores you specify. For instance, this is using six underscores in a row, meaning it returns any Matter Name that has 5 or less characters.
If you want to reverse it, change the operator to "Contains". Then that would show you every Matter Name with at least six characters, or more.
If you want to find items with an exact number of characters, then unfortunately the "equals" operator still doesn't seem to work correctly, but you can instead use two conditions: one "Not Contains" and the other "Contains". Just make sure you use AND logic, not OR. For example, here's how to filter down to items that are exactly three characters long.
It definitely seems like something is going wrong with the "equals" filter. Here's another experiment I tried.
If I filter for "equals Unknown" then it pulls up results:
But if I change it to "equals Unkno_n" (replacing the 'w' with an underscore), it instead says No Results:
If I instead change it to use "contains" logic instead, then it'll work correctly, though of course this brings in too many results.
Strange! It definitely seems like the "equals" operator isn't interpreting the wildcard character correctly, and maybe instead interpreting it as a literal character (despite me not using an escape character). Is this maybe a bug/defect that has been fixed in later versions of Sisense?
Okay, nevermind, figured it out on my own after more experimentation 🙂
For anyone else trying to do this: use a "Doesn't Contain" filter, and then chain the number of underscores for the number of characters you're looking for. This will show you with everything that has LESS than the number of underscores you specify. For instance, this is using six underscores in a row, meaning it returns any Matter Name that has 5 or less characters.
If you want to reverse it, change the operator to "Contains". Then that would show you every Matter Name with at least six characters, or more.
If you want to find items with an exact number of characters, then unfortunately the "equals" operator still doesn't seem to work correctly, but you can instead use two conditions: one "Not Contains" and the other "Contains". Just make sure you use AND logic, not OR. For example, here's how to filter down to items that are exactly three characters long.
