Transposing a Data Table

jenkins_macedo — Wed, 19 Oct 2016 00:20:22 GMT

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

Re: Transposing a Data Table

jenkins_macedo — Wed, 12 Aug 2015 21:38:30 GMT

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.

Re: Transposing a Data Table

jenkins_macedo — Wed, 19 Oct 2016 00:20:47 GMT

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.

Re: Transposing a Data Table

jenkins_macedo — Wed, 12 Aug 2015 22:54:08 GMT

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

topic Re: Transposing a Data Table in Discussions

Transposing a Data Table

Re: Transposing a Data Table

Re: Transposing a Data Table

Re: Transposing a Data Table