Choose Language Hide Translation Bar
Highlighted
djmomo
Level I

Summary Table, Rename column, and transpose table

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?

BINTOFRWEDMP
1001Y131A
1001N 31A
1001N 31A
1001Y131A
1002N 31A
1002N 31B
1002N 31B
1002Y131B
1003Y131A
1003Y131B

 

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

BINN RowsMPSum(RWED)
1001431A2
1002131A0
1002331B1
1003131A1
1003131B1

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

BINN RowsMPTRWEDRWRat
1001431A250
1002131A00
1002331B133.33
1003131A1100
1003131B1100

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

BIN31A31B
1001500
1002033.33
1003100100

 

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Summary Table, Rename column, and transpose table

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

View solution in original post

3 REPLIES 3
Highlighted
txnelson
Super User

Re: Summary Table, Rename column, and transpose table

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

View solution in original post

Highlighted
djmomo
Level I

Re: Summary Table, Rename column, and transpose table

@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.

Highlighted
djmomo
Level I

Re: Summary Table, Rename column, and transpose table

 
Article Labels

    There are no labels assigned to this post.