I have a table with Column A (char). I want to add a new column in number value which represents the grouping of column A. How to do this automatically? Column A has too many characters for me to manually group them.
| column A | new column |
| kkk | 1 |
| dd | 2 |
| kkk | 1 |
| cc | 3 |
May be can use Cols --> Utilities --> Labels to Code (it automatically assign a code based alphabetical order)?
Then you create new column using below formula;
:new column << Set Formula( Col Stored Value( :Column A ) );
one more question is that the code is sequential given. I wonder if there is a way to assign the number based on the highest occurrence in column A.
to assign the number based on highest occurrence in column A need some work around.
1. create a summary table by group, "Column A" --> sort N column "Descending"
2. add formula column: new result = Row ();
3. join both tables or update main table to match "Column A".
If you're using scripting, it needs few lines as below;
// → Data Table( "Untitled" )
New Table( "Untitled" );
// Data table summary
// → Data Table( "Untitled By (Column A)" )
Data Table( "Untitled" ) << Summary(
Group( :Column A ),
Freq( "None" ),
Weight( "None" )
);
// Sort data table
Data Table( "Untitled By (Column A)" ) << Sort(
By( :N Rows ),
Replace Table,
Order( Descending )
);
// New column: new result
Data Table( "Untitled By (Column A)" ) << New Column( "new result",
Numeric,
"Continuous",
Format( "Best", 12 ),
Formula( Row() )
);
// Update data tables
Data Table( "Untitled" ) << Update(
With( Data Table( "Untitled By (Column A)" ) ),
Match Columns( :Column A = :Column A )
);hope it helps.
