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