230296_en-US

liliconcom

Increment by levels of character column (scripting)

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

Re: Increment by levels of character column (scripting)

 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!

Re: Increment by levels of character column (scripting)

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

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)

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 ) );

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.

1. Use Recode to create a new column, changing the values found for the new numeric values wanted
2. 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.