Subscribe Bookmark RSS Feed

New table of Column Names and Column Totals

hacketkm

Community Trekker

Joined:

Sep 14, 2012


I have a data table that looks like the one below.

DateItemAItemBItemC
1/1/2005

2

10112
1/2/2005210112
1/3/2005310112
1/4/2005310112
1/5/2005310112

I want to make a new data table with two columns:

ItemTotal
ItemA13
ItemB50
ItemC560

I have tried using Tabulate() and Transpose() and Summary() and cannot find the best way to do this. Can anyone help me out?

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Slightly improved script.  Replace My Table with the name of your original data table.  The tabulate command below will sum up the data column, split out by the Label (ItemA, ItemB, ItemC).

dt = data table("My Table");

stack_dt = dt << Stack(

    columns( :ItemA, :ItemB, :ItemC ),

    Source Label Column( "Label" ),

    Stacked Data Column( "Data" )

);

stack_dt << Tabulate(

    Show Control Panel( 0 ),

    Add Table(

        Column Table( Analysis Columns( :Data ) ),

        Row Table( Grouping Columns( :Label ) )

    )

);

4 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

You need to reformat the table before running tabulate.  Click on Tables > Stack.  Select ItemA, ItemB, ItemC and click Stack Columns, then OK.  Now run tabulate on this new table.  Drag the Label column into the drop zone for rows.  Then drag the Data column into the column of 5s.

2617_pastedImage_8.png

The JSL code is:

dt << Stack(

    columns( :ItemA, :ItemB, :ItemC ),

    Source Label Column( "Label" ),

    Stacked Data Column( "Data" )

);

Tabulate(

    Show Control Panel( 0 ),

    Add Table(

        Column Table( Analysis Columns( :Data ) ),

        Row Table( Grouping Columns( :Label ) )

    )

);

hacketkm

Community Trekker

Joined:

Sep 14, 2012

With the help of your code, I am close to what I need. But, where in the code do I specify that I want the sum of "Data"??

Thank you for the help.

Solution

Slightly improved script.  Replace My Table with the name of your original data table.  The tabulate command below will sum up the data column, split out by the Label (ItemA, ItemB, ItemC).

dt = data table("My Table");

stack_dt = dt << Stack(

    columns( :ItemA, :ItemB, :ItemC ),

    Source Label Column( "Label" ),

    Stacked Data Column( "Data" )

);

stack_dt << Tabulate(

    Show Control Panel( 0 ),

    Add Table(

        Column Table( Analysis Columns( :Data ) ),

        Row Table( Grouping Columns( :Label ) )

    )

);

hacketkm

Community Trekker

Joined:

Sep 14, 2012

Thank you so much! This worked great.