Summary Table, Rename column, and transpose table

djmomo

Hello world, Novice to JSL. I have a data table in the below format. Columns are BIN, TOF (had error or not), RWED( Redid the welding), and MP (assembled by).  Its not outputting the final results and end after Step 1 (described below).  How can I fix it?

BIN	TOF	RWED	MP
1001	Y	1	31A
1001	N		31A
1001	N		31A
1001	Y	1	31A
1002	N		31A
1002	N		31B
1002	N		31B
1002	Y	1	31B
1003	Y	1	31A
1003	Y	1	31B

 

I'm trying following steps to achieve the final results i want which is RWED by percentage of total number or assembled units in that BIN.

Step 1: create a summary table

BIN	N Rows	MP	Sum(RWED)
1001	4	31A	2
1002	1	31A	0
1002	3	31B	1
1003	1	31A	1
1003	1	31B	1

Step 2: Rename summary table "Sum(RWED)" to "TRWED" and add column with formula "RWRat" to calculate re weld rate by total number of units weds by BIN by MP

BIN	N Rows	MP	TRWED	RWRat
1001	4	31A	2	50
1002	1	31A	0	0
1002	3	31B	1	33.33
1003	1	31A	1	100
1003	1	31B	1	100

Step 3: Transpose the table to final output and save the table

BIN	31A	31B
1001	50	0
1002	0	33.33
1003	100	100

 

I did the following script

 

dt1 = Open( "smdt.csv" ); //Open the data table

Wait( 2 );

//Summary table creation
dt2 << Summary( Group( :BIN, :MP ), Sum( :RWED ), Freq( "None" ), Weight( "None" ), );

Column( dt2, "sum(:RWED)" ) << set name( "TRWED" );//renaming the summary table column
dt2 << New Column( "RWRat", Numeric, Continuous, Formula( Round( (:Name( "TRWED)" ) / :N Rows) * 100, 2 ) ) );//add new column to with formula

//Transpose the final results
Transpose( columns( :Name( "Sum(RWED)" ) ), By( :BIN ), Label( :MP ), Output Table( "RWRT By (BIN, MP)" ) );

dt2 << Save( "Stb.csv" );//Save the table

 

txnelson

names default to here(1);

dt1 = New Table( "Example",
	Add Rows( 10 ),
	New Script(
		"Source",
		Data Table( "Untitled 31" ) << Split(
			Split By( :Column 2 ),
			Split( :Column 1 ),
			Sort by Column Property
		)
	),
	New Column( "Bin",
		Character,
		"Nominal",
		Set Values(
			{"1001", "1001", "1001", "1001", "1002", "1002", "1002", "1002", "1003",
			"1003"}
		)
	),
	New Column( "TOF",
		Character,
		"Nominal",
		Set Values( {"Y", "N", "N", "Y", "N", "N", "N", "Y", "Y", "Y"} )
	),
	New Column( "RWED",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Selected,
		Set Values( [1, ., ., 1, ., ., ., 1, 1, 1] )
	),
	New Column( "MP",
		Character,
		"Nominal",
		Set Values(
			{"31A", "31A", "31A", "31A", "31A", "31B", "31B", "31B", "31A", "31B"}
		)
	)
);

dt2 = dt1 << Summary(
	Group( :Bin, :MP ),
	Sum( :RWED ),
	Freq( "None" ),
	Weight( "None" )
);

dt2:Name("Sum(RWED)") << set name( "TRWED" );

dt2 << NewColumn("RWRat", Numeric, Continuous, Formula(Round(:TRWED / :N Rows* 100, 2) ));

dt3 = dt2 << Transpose(
	columns( :RWRat ),
	By( :Bin ),
	Label( :MP ),
	Output Table( "\!"RWRT By (TXN_WW, MP" )
);