Table Transform Query

Chaytorial

I am having difficulty reformatting a table data in JMP. I'm hoping to transform a table from a 'standings' format to 'weekly score format', e.g.

 

From:

 

Week     1st_Name     1st_Score     2nd_Name     2nd_Score     3rd_Name     3rd_Score

1               David               5                  Tim                  2                  Rachel            1

2               Tim                  7                Alexis                 4                 Rachel             2

3               Tina                 6                Rachel               5                 David                2

 

To:

 

Week     David     Tim     Rachel     Alexis     Tina

1                5           2           1            0            0

2                0           7           2            4            0

3                2           0           5            0            6

 

I can order the columns differently in the original data pull if that makes it easier to reformat.

If anyone is able to help with this I would be very grateful.

txnelson

My error....but a simple fix.....just add the column Week as the grouping column in the Split Dialog.  Below is the full script with the addition of the Group column to the Split code.

Names Default To Here( 1 );
dt = New Table( "Example",
	Add Rows( 3 ),
	New Column( "Week", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3] ) ),
	New Column( "1st_Name", Character, "Nominal", Set Values( {"David", "Tim", "Tina"} ) ),
	New Column( "1st_Score", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [5, 7, 6] ) ),
	New Column( "2nd_Name", Character, "Nominal", Set Values( {"Tim", "Alexis", "Rachel"} ) ),
	New Column( "2nd_Score", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [2, 4, 5] ) ),
	New Column( "3rd_Name", Character, "Nominal", Set Values( {"Rachel", "Rachel", "David"} ) ),
	New Column( "3rd_Score", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [1, 2, 3] ) )
);

dtStack = dt << Stack(
	columns(
		:"1st_Name"n,
		:"1st_Score"n,
		:"2nd_Name"n,
		:"2nd_Score"n,
		:"3rd_Name"n,
		:"3rd_Score"n
	),
	Source Label Column( "Label" ),
	Stacked Data Column( "Data" ),
	Number of Series( 2 ),
	Output Table( "Stacked Data" )
);

dtFinal = dtStack << Split(
	Split By( :Data ),
	Split( :Data 2 ),
	Group( :Week ),
	Remaining Columns( Drop All ),
	Sort by Column Property
);

For( i=1, i<=N Cols(dtFinal), i++,
	missing = dtFinal << get rows where( isMissing(as column( i )));
	try(column(i)[missing]=0);
);