Subscribe Bookmark RSS Feed

Transposing a Data Table

jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

The data in this question are fictitious.

I have a data table in the form of:

Campaign CodePrograms# Confirmed Enrollments# First Time Enrollments$ Confirmed Enrollments$ Program Only$ Total Enrollment Amt$ Total Net Order
ZZ10AF012,0002,0002,0002,000
ZZ10DOM103,5003,5003,5003,500
ZZ10INTL2210,00010,00010,00010,000
YY15AF11250250250250
YY15DOM11300300300300
YY15INTL2110,00010,00010,00010,000
AC10AF111000100010001000
AC10DOM215000500050005000
AC10INTL111000100010001000
ZZ10 AF102500250025002500

I want to transpose this table such that the outputted data table is a summary of the data based on

Campaign Code     Label                                                  AF                         DOM                    INTL              

ZZ10                     # Confirmed Enrollments                       2                              1                         2           

                            # First Time Enrollments                        1                              0                         2

                            $ Confirmed Enrollments                       4,500                      3,500                10,000                            

                            $ Program Only                                    4,500                      3,500                 10,000       

                            $ Total Enrollment Amt                         4,500                      3,500                 10,000

                            $ Total Net Order                                 4,500                      3,500                 10,000

             

YY15                     # Confirmed Enrollments                       1                            1                         2           

                            # First Time Enrollments                        1                            1                         1

                            $ Confirmed Enrollments                       250                        300                10,000                            

                            $ Program Only                                    250                        300                 10,000       

                            $ Total Enrollment Amt                         250                        300                 10,000

                            $ Total Net Order                                 250                        300                 10,000

etc

                      

For the original data table, I am using this script to develop the transposed out data table, but the problem is for each time I transposed the table, it seems to only calculate the values based on each of the # and $ variables instead of capturing all the values associated with the campaign codes and programs. Here is the script:

Data Table( "Enrollment-Segmented" ) << Transpose(

  columns(

  :Name( "# Confirmed Enrollments" ),

  :Name( "# First Time Enrollments" ),

  :Name( "$ Confirmed Enrollments" ),

  :Name( "$ Program Only" ),

  :Name( "$ Total Enrollment Amt" ),

  :Name( "$ Total Net Order" )

  ),

  By( :Mail Code ),

  Label( :Program Line ),

  Output Table( "Transpose of Enrollment-Segmented" )

)

And these are screenshots of my actual data table and the transposed data table highlighting what are included in the original data table and missing in the transposed data table. As you can see from the original data table, the # Confirmed Enrollments for Mail Code AAE for Afloat is (2+0=2), Domestic (24+9=33), and International (198+34+15=247). However, the transposed table is only bring out the count of one of each program line. I want it to count all instead of just one.

Original Data Table

9473_Original Data Table.jpg

Transposed Table from the original table

In this transposed table, the result only summarizes one count for each mail code per program line instead of counting/summing all. For an example and from the screenshot below, you can see for Mail Code AAE it counted only 2 for # Confirmed Enrollments for Afloat, which is correct, but only brought forward 24 for Domestic instead of 33 and 15 for International instead of 247. Any suggested solution or script would be helpful. The original data table contains 31,000 rows and 8 columns.

9474_Transposed Table.jpg

Thanks,

Jenkins

Jenkins Macedo
1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Gotcha! Using the Table Summary feature in JMP Pro 12.  I listed Mail Code in my actual data table comparable to the Campaign Code in the example along with the Program Line same as the Program in my example and the selected Sum under Statistics for all the other attributed that I am interested in and the result is what i was expecting. Now, I have to merge/join this new table with the circulation table, which is the next stage using the Mail Code unique to each table. Thanks guys.

9477_Table Summary.jpg

9478_Answer.jpg

Jenkins Macedo
3 REPLIES
jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

I am thinking that I have to first summarize the data table by campaign code as in the example by all the specified attributes and than transpose the results. I am not sure.

Jenkins Macedo
Solution

Gotcha! Using the Table Summary feature in JMP Pro 12.  I listed Mail Code in my actual data table comparable to the Campaign Code in the example along with the Program Line same as the Program in my example and the selected Sum under Statistics for all the other attributed that I am interested in and the result is what i was expecting. Now, I have to merge/join this new table with the circulation table, which is the next stage using the Mail Code unique to each table. Thanks guys.

9477_Table Summary.jpg

9478_Answer.jpg

Jenkins Macedo
jenkins_macedo

Community Trekker

Joined:

Jul 13, 2015

Transposing in this case didn't work, maybe someone may have an scriptable answer, but the table summary feature did the job.

Jenkins Macedo