The data in this question are fictitious.
I have a data table in the form of:
| Campaign Code | Programs | # Confirmed Enrollments | # First Time Enrollments | $ Confirmed Enrollments | $ Program Only | $ Total Enrollment Amt | $ Total Net Order |
|---|---|---|---|---|---|---|---|
| ZZ10 | AF | 0 | 1 | 2,000 | 2,000 | 2,000 | 2,000 |
| ZZ10 | DOM | 1 | 0 | 3,500 | 3,500 | 3,500 | 3,500 |
| ZZ10 | INTL | 2 | 2 | 10,000 | 10,000 | 10,000 | 10,000 |
| YY15 | AF | 1 | 1 | 250 | 250 | 250 | 250 |
| YY15 | DOM | 1 | 1 | 300 | 300 | 300 | 300 |
| YY15 | INTL | 2 | 1 | 10,000 | 10,000 | 10,000 | 10,000 |
| AC10 | AF | 1 | 1 | 1000 | 1000 | 1000 | 1000 |
| AC10 | DOM | 2 | 1 | 5000 | 5000 | 5000 | 5000 |
| AC10 | INTL | 1 | 1 | 1000 | 1000 | 1000 | 1000 |
| ZZ10 | AF | 1 | 0 | 2500 | 2500 | 2500 | 2500 |
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
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.
Thanks,
Jenkins
Accepted Solutions
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.
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.