Choose Language Hide Translation Bar
Highlighted

## 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?

 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``````

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted

## Re: Summary Table, Rename column, and transpose table

``````names default to here(1);

dt1 = New Table( "Example",
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
3 REPLIES 3
Highlighted

## Re: Summary Table, Rename column, and transpose table

``````names default to here(1);

dt1 = New Table( "Example",
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
Highlighted

## 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
Article Labels

There are no labels assigned to this post.