topic Re: Summary Table, Rename column, and transpose table in Discussions

Summary Table, Rename column, and transpose table

djmomo — Tue, 14 Jan 2020 19:36:00 GMT

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

Re: Summary Table, Rename column, and transpose table

txnelson — Tue, 14 Jan 2020 19:25:55 GMT

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" )
);

Re: Summary Table, Rename column, and transpose table

djmomo — Tue, 14 Jan 2020 19:42:13 GMT

@txnelson Thank you for the quick response.

After creating the RWRat column I see that its not adding the calculated values to the column. Is it because " N Rows" column? "N Rows" column is created when I do the summary table. Do I have to rename that column if I want to reuse it in the formula?

Final output table format is correct for me but my table values comes as blank.

Also I TXN_WW==BIN. That was my mistake.

Re: Summary Table, Rename column, and transpose table

djmomo — Tue, 14 Jan 2020 19:47:15 GMT