turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- how to exclude rows from Col Rank formula in JSL?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 19, 2015 6:41 PM
(2479 views)

I'm trying to run a Col Rank formula on a "BLOCKERASECYCLE" column and then output the result to a new column "TEMPCOLUMN"

However it runs Col Rank even on rows that I hide and exclude (rows are not "VERSION1").

How can I get Col Rank to skip rows that I hide and exclude (non "VERSION1" rows)?

BLOCKERASECYCLE_COL = Column("BLOCKERASECYCLE");

foundrows = dt << Get Rows Where(:PART_VERSION=="VERSION1");

dt<<Select Rows(foundrows);

dt<<Invert Row Selection;

dt<<Exclude;//Exclude non-VERSION1 rows

dt<<Hide;//Hide non-VERSION1 rows

dt<<Invert Row Selection;

dt<<New Column("TEMPCOLUMN", Numeric, Formula(Col Rank(BLOCKERASECYCLE_COL)));

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 20, 2015 11:19 AM
(4052 views)

Solution

Hi, Steve.

The following formula will calculate the rank for only un-excluded rows:

Col Rank(If(Excluded(), ., :height))

You can verify that the formula is correct by running a Distribution on your BLOCKERASECYCLE column and then save the ranks (red triangle > Save > Ranks). This will generate a new column that contains the ranks for un-excluded rows, which you can compare to the formula column. Of course, you could just use the Distribution platform to generate the rank column in the first place, as well.

Hope that helps.

Wendy

Wendy

3 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 20, 2015 5:11 AM
(2026 views)

Try this. A bit klugy but it works

dt = current data table**()**;

foundrows = dt << **Get Rows Where(**:PART_VERSION=="VERSION1"**)**;

nv1 = nrows**(**foundrows**)**;

dt << **New Column(** "TEMPCOLUMN", Numeric,

Formula**(** if **(**:PART_VERSION != "VERSION1", Col Rank**(** :BLOCKERASECYCLE **)** - nv1 **)** **)** **)**;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 20, 2015 11:19 AM
(4053 views)

Hi, Steve.

The following formula will calculate the rank for only un-excluded rows:

Col Rank(If(Excluded(), ., :height))

You can verify that the formula is correct by running a Distribution on your BLOCKERASECYCLE column and then save the ranks (red triangle > Save > Ranks). This will generate a new column that contains the ranks for un-excluded rows, which you can compare to the formula column. Of course, you could just use the Distribution platform to generate the rank column in the first place, as well.

Hope that helps.

Wendy

Wendy

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

May 20, 2015 11:53 AM
(2026 views)

Thanks, replacing the last statement with this new one is able to ignore the excluded rows:

dt<<New Column("TEMPCOLUMN", Numeric, Formula(Col Rank(If(Excluded(), ., BLOCKERASECYCLE_COL))));