I see now Jim has already presented a solution. Here's a different approach using Tabulate and Combine Columns (Multiple Response Column) that hopefully works also on the full data set. // Groups is the example table with 2 nominal columns
DT_GroupCategory = Data Table( "Groups" );
// creates a cross table
Tab_DT_GroupCategory = DT_GroupCategory << Tabulate(
Add Table(
Column Table( Grouping Columns( :Category ) ),
Row Table( Grouping Columns( :Group ) )
)
);
// make data table from it
DT_GroupCategory_CrossTab = Tab_DT_GroupCategory << Make Into Data Table;
DT_GroupCategory_CrossTab << Add Multiple Columns( "N Categories", 1, after( 1 ), Numeric );
Names_DT_GroupCategory_CrossTab = DT_GroupCategory_CrossTab << Get Column Names;
// replace 0 by missing and everything else by column name (probably not the most efficient way to do this)
For( i=3, i<=N Items( Names_DT_GroupCategory_CrossTab ), i++,
Column( DT_GroupCategory_CrossTab, i ) << Data Type( "Character" );
For( j=1, j<=N Rows( DT_GroupCategory_CrossTab ), j++,
If( Column( DT_GroupCategory_CrossTab, i )[j] == "0",
Column( DT_GroupCategory_CrossTab, i )[j] = "",
Column( DT_GroupCategory_CrossTab, i )[j] = Char( Names_DT_GroupCategory_CrossTab[i] );
)
)
);
// make multiple response column with Cols -> Utilities -> Combine Columns
DT_GroupCategory_CrossTab << Combine Columns(
delimiter( "," ),
Columns( 2::N Items( Names_DT_GroupCategory_CrossTab ) ),
Selected Columns are Indicator Columns( 0 ),
Column Name( "Values(Category)" )
);
// add formula to get N Categories. +1 is added for one less comma, then div by 2 to get number of categories
Column( DT_GroupCategory_CrossTab, "N Categories" ) << Formula( (Length( :Name( "Values(Category)" ) ) + 1) / 2 );
Tab_DT_GroupCategory << Close Window;
... View more