topic Re: How to exclude rows based on a formula like in excel in Discussions

How to exclude rows based on a formula like in excel

Blueseashell3 — Thu, 08 Jun 2023 16:38:43 GMT

Hello All,

I have not run anything in JSL, as so far everything I've needed has been available as a series of buttons in the existing UI. I am hoping that what I want is still in that realm, but if it isn't, I could use instructions that a child could follow.

I want to be able to write a formula in the data filter that would exclude anything that either satisfies (or doesn't satisfy) that formula. Is there an option like that in the UI, or will I need to script something for that?

For example, if I want to say "exclude rows that do not contain the mode of X column" or "exclude rows that are not within 1 st.dev of the mean of Y column", how can I do that?

Thanks!

-Blue

Re: How to exclude rows based on a formula like in excel

jthi — Fri, 03 Feb 2023 14:56:08 GMT

Create new formula column with your calculation. You could make it show 1 when it should be filtered and 0 when not. Then you can add Data Filter to your data table and use that as a filter.

Col Mode(:age) == :age

If you use If statement you can use character column with what ever text you want or Value Codes column property:

Re: How to exclude rows based on a formula like in excel

danschikore — Fri, 03 Feb 2023 15:41:59 GMT

Another approach similar to that suggested by @jthi is to use a transform column within the data filter:

Define the formula just as you would when adding a column to the table:

The new column will appear only in the filter. You can right-click to give the new column a different name, and then add it to the filter like any other column: