BookmarkSubscribe
Choose Language Hide Translation Bar
hacketkm
Community Trekker

New table of Column Names and Column Totals


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?

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
pmroz
Super User

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

    )

);

0 Kudos
4 REPLIES 4
Highlighted
pmroz
Super User

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.

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

    )

);

0 Kudos
hacketkm
Community Trekker

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.

0 Kudos
pmroz
Super User

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

    )

);

0 Kudos
hacketkm
Community Trekker

Re: New table of Column Names and Column Totals

Thank you so much! This worked great.

0 Kudos