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

- JMP User Community
- :
- Discussions
- :
- Increment by levels of character column (scripting)

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

Highlighted

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

Oct 23, 2019 5:30 AM
(1820 views)

Maybe I'm again not seeing the wood before the trees; I just want to apply some numeric increment for every distinct level of a character column.

Same levels get the same increment, different levels get different numbers.

Assumption: Data are sorted by "Character_Column_Have". "Numeric_Increment_Want" should start with 1.

Character_Column_Have Numeric_Increment_Want

AAA 1

AAA 1

ABC 2

BBB 3

CCD 4

EEE 5

EEE 5 ...

The only result I achieved so far was to repeatedly crash my JMP by using "col **bleep** rank".

JMP and me could do better, right?

Thanks for every pointer!

Newbie

2 ACCEPTED SOLUTIONS

Accepted Solutions

Highlighted

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

Here is the formula for the Numeric_Increment_Want column

```
If( Row() == 1,
Summarize( myGroups = by( :Period ) )
);
Contains( myGroups, :Period );
```

I had to change the previous code because the reference to "Groups" in the old code, conflicts with the Dolphins data table having a column named Groups. So I changed the variable name to myGroups.

Attached is the data table with the new column added

Jim

Highlighted

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

```
New Column( "Increment",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
If( Row() == 1,
Summarize( myGroups = by( :Period ) )
);
Contains( myGroups, :Period );
)
);
```

I cannot explain why it now works. I also adjusted for "Groups". Weired. Anyway, I solved it like that and it works like a charm. Jim, thank you very much. Again!

5 REPLIES 5

Highlighted
##

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

Re: Increment by levels of character column (scripting)

Here is the formula that I would use

```
If( Row() == 1,
Summarize( groups = by( :Character_Column_Have ) )
);
Contains( groups, :Character_Column_Have );
```

There are also other methods.

- Use Recode to create a new column, changing the values found for the new numeric values wanted
- Use the Summary Platform with Character_Column_Have as the Group column to create a new data table with one row per value of Character_Column_Have. Then create a new column that has a formula of Row(). Finally use the Update Platform to join the summary table with the original table.

Jim

Highlighted
##

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

Re: Increment by levels of character column (scripting)

I tried all your approaches but it seems I cannot make them work.

One approach puzzles me conceptually. It addresses two input columns, while only character column is available for input.

Could you demonstrate one approach on the JMP dataset "Dolphins" please (excerpt below)?

Travel Morning 6

Feed Morning 28

... Travel Evening 13

Feed Evening 56

Social Evening 10

For the "Dolphins" levels Morning, Noon, Afternoon, Evening I'd expect the increments 1,2,3 resp. 4 (subject to sorting order)

Highlighted

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

Here is the formula for the Numeric_Increment_Want column

```
If( Row() == 1,
Summarize( myGroups = by( :Period ) )
);
Contains( myGroups, :Period );
```

I had to change the previous code because the reference to "Groups" in the old code, conflicts with the Dolphins data table having a column named Groups. So I changed the variable name to myGroups.

Attached is the data table with the new column added

Jim

Highlighted

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

```
New Column( "Increment",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula(
If( Row() == 1,
Summarize( myGroups = by( :Period ) )
);
Contains( myGroups, :Period );
)
);
```

I cannot explain why it now works. I also adjusted for "Groups". Weired. Anyway, I solved it like that and it works like a charm. Jim, thank you very much. Again!

Highlighted
##

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

Re: Increment by levels of character column (scripting)

I like Jim's answer. You could also use a simple formula:

```
If(
Row() == 1, 1,
:Have == Lag( :Have ), Lag( :Want ),
Lag( :Want ) + 1
)
```

Or the equivalent script:

`For Each Row( If( Row() == 1, 1, :Have == Lag( :Have ), Lag( :Want ), Lag( :Want ) + 1 ) );`

Learn it once, use it forever!

Article Labels

There are no labels assigned to this post.