Subscribe Bookmark RSS Feed

Delete Double Row in a data table

antoinesofradir

Community Trekker

Joined:

Apr 26, 2013

Hello,


I have lots of Double row in my data table and i would like to clean it.
I only want distinct value in my data table.

Is there a function to do it ?

Thanks for your help !

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

The problem is that set name is sent to dt (the original table) and not to the summary table (dt = current data table() is only executed once, i.e. the variable dt is locked to that table unless dt is redefined).

Try this:

dt_sum = dt << summary( group( dt << get column names ), Link to original data table( 0 ) );

dt_sum << Set Name( "New table" );

6 REPLIES
ms

Super User

Joined:

Jun 23, 2011

I think the easiest way get rid of duplicates is to use Summary (in tables menu) and add all columns to the Group field. You will get a new table that needs to be saved and one extra column N Rows that can be deleted. Row state columns and column formulas will be retained.

A script example:

dt = Current Data Table();

dt << summary( group( dt << get column names ), Link to original data table( 0 ) );


antoinesofradir

Community Trekker

Joined:

Apr 26, 2013

Thank you for your answer, it works !
I tried to name the new table but it doesn't work :

dt = Current Data Table();

dt << summary( group( dt << get column names ), Link to original data table( 0 ) );

dt<< Set Name("New table");

What is the problem ? Thank you for your help !

Solution

The problem is that set name is sent to dt (the original table) and not to the summary table (dt = current data table() is only executed once, i.e. the variable dt is locked to that table unless dt is redefined).

Try this:

dt_sum = dt << summary( group( dt << get column names ), Link to original data table( 0 ) );

dt_sum << Set Name( "New table" );

antoinesofradir

Community Trekker

Joined:

Apr 26, 2013

Thank you very much !

saitcopuroglu

Community Trekker

Joined:

Sep 29, 2014

Dear MS,

Related to the subject, I can set names of summary tables, subset tables but somehow I can't do it:

After creating a tabulate and making a table of it, I am unable to add a name to the table created. Wherever I put "Set Name" command it does not work:

<<Tabulate(

    Change Item Label(

        Grouping Columns( :Contract( "All" ), "All" ),

        Grouping Columns( :Room Type( "All" ), "All" ),

        Grouping Columns( :month( "All" ), "All" )

    )

    Remove Column Label( Grouping Columns( :Room Type, :month, :Resort ) ),

    Uniform plot scale( 0 ),

    Set Format(

        Sum( :NetRevenue( 22, 0 ), :Name( "FChNight/AdNight" )(18, 0), :ADB( 19, 0 ) ),

        Column %( :Name( "FChNight/AdNight" )(16, 1), :ADB( 17, 0 ), :RoomNight( 21, 0 ) ),

        Mean( :NetRevenue( 20, 2 ), :Name( "FChNight/AdNight" )(16, 1), :Disc%( 16, 0 ), :ADB( 17, 0 ), :RoomNight( 21, 0 ) ),

        Row %( :Name( "FChNight/AdNight" )(16, 0), :RoomNight( 21, 0 ) ),

        Name( "% of Total" )(9, 2)

    ),

    Include missing for grouping columns( 1 ),

    Add Table(

        Column Table( Grouping Columns( :Resort ), Analysis Columns( :NetRevenue ), Statistics( Sum, Column % ) ),

        Column Table( Analysis Columns( :AvgRN ), Statistics( Mean ) ),

        Column Table( Analysis Columns( :DoubleOcc ), Statistics( Mean ) ),

        Column Table( Analysis Columns( :Name( "FChNight/AdNight" ) ), Statistics( Mean ) ),

        Column Table( Grouping Columns( :EB ), Add Aggregate Statistics( :EB ), Statistics( Name( "% of Total" ) ) ),

        Column Table( Analysis Columns( :Disc% ), Grouping Columns( :month ), Add Aggregate Statistics( :month ), Statistics( Mean ) ),

        Column Table( Analysis Columns( :ADB ), Grouping Columns( :month ), Add Aggregate Statistics( :month ), Statistics( Mean ) ),

        Column Table( Analysis Columns( :RoomNight ), Grouping Columns( :month ), Statistics( Row % ) ),

        Column Table( Analysis Columns( :RoomNight ), Grouping Columns( :Room Type ), Statistics( Row % ) ),

        Row Table( Grouping Columns( :Contract ), Add Aggregate Statistics( :Contract ) )

    )  << Make Into Data Table,

   

);

ms

Super User

Joined:

Jun 23, 2011

Try this sequence of commands:

dt = Open("$SAMPLE_DATA/Car Poll.jmp");

tab = dt << Tabulate(

    Show Control Panel(0),

    Add Table(

        Column Table(Grouping Columns(:sex, :marital status)),

        Row Table(Grouping Columns(:country, :size))

    )

);

dt_sum = tab << Make Into Data Table;

dt_sum << Set Name("New table");