5757_en-US

liliconcom

New table of Column Names and Column Totals<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

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

Date	ItemA	ItemB	ItemC
1/1/2005	2	10	112
1/2/2005	2	10	112
1/3/2005	3	10	112
1/4/2005	3	10	112
1/5/2005	3	10	112

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

Item	Total
ItemA	13
ItemB	50
ItemC	560

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

Re: New table of Column Names and Column Totals

Thank you so much! This worked great.

Re: New table of Column Names and Column Totals

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

    )

);

Re: New table of Column Names and Column Totals

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.

Re: New table of Column Names and Column Totals

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.

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

    )

);