Question bout Table Merge, how to use JSL to merge multiple column data into one column?Question bout Table Merge, how to use JSL to merge multiple column data into one column?
if I have one table, some column name is very similar, for example one column name is WR689700@WFI*HHC 56XYZ, another column name is WR@689799@WFI*HH 56XY W, the cell content in one column shows actual value and same row in another column shows "NA", the question is how to merge cell content in a new column and remove "NA", like below shows. because have >20 columns in the table, need use JSL to judge and group the similar column then merge the cell content to a new one.
thanks
| | | Original Table | | | | After Merge Table |
| ID_1 | ID_2 | WR@689700@WFI*HHC 56XYZ | WR@689799@WFI*HH 56XYZ W | WR@780990@UW*FFY 78XYZ | WR@780666@UW*FFY 78XYZ | --> | ID_1 | ID_2 | WR@689700@WFI*HHC 56XYZ | WR@689799@WFI*HH 56XYZ W | WR@780990@UW*FFY 78XYZ | WR@780666@UW*FFY 78XYZ | WR@1 | WR@2 |
| 22164 | 115 | 72_22P | NA | SS_YY | NA | 22164 | 115 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 25335 | 33 | 72_22P | NA | NA | 88_CC | 25335 | 33 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 24870 | 872 | 72_22P | NA | SS_YY | NA | 24870 | 872 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 24861 | 591 | NA | 72_88P | SS_YY | NA | 24861 | 591 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 12928 | 274 | 72_22P | NA | SS_YY | NA | 12928 | 274 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 20927 | 818 | NA | 72_88P | SS_YY | NA | 20927 | 818 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 22167 | 14 | 72_22P | NA | SS_YY | NA | 22167 | 14 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 12930 | 120 | 72_22P | NA | NA | 88_CC | 12930 | 120 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 28447 | 155 | 72_22P | NA | SS_YY | NA | 28447 | 155 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 33380 | 138 | NA | 72_88P | NA | 88_CC | 33380 | 138 | NA | 72_88P | NA | 88_CC | 72_88P | 88_CC |
| 18186 | 769 | 72_22P | NA | NA | 88_CC | 18186 | 769 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 27557 | 91 | NA | 72_88P | NA | 88_CC | 27557 | 91 | NA | 72_88P | NA | 88_CC | 72_88P | 88_CC |
| 14863 | 116 | 72_22P | NA | NA | 88_CC | 14863 | 116 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 29928 | 204 | NA | 72_88P | SS_YY | NA | 29928 | 204 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 15809 | 70 | 72_22P | NA | NA | 88_CC | 15809 | 70 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 21701 | 269 | 72_22P | NA | SS_YY | NA | 21701 | 269 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 27937 | 110 | NA | 72_88P | SS_YY | NA | 27937 | 110 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 20136 | 454 | 72_22P | NA | SS_YY | NA | 20136 | 454 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 27934 | 866 | NA | 72_88P | NA | 88_CC | 27934 | 866 | NA | 72_88P | NA | 88_CC | 72_88P | 88_CC |
| 18186 | 759 | 72_22P | NA | SS_YY | NA | 18186 | 759 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 27937 | 106 | NA | 72_88P | SS_YY | NA | 27937 | 106 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 11921 | 170 | 72_22P | NA | SS_YY | NA | 11921 | 170 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 15809 | 275 | 72_22P | NA | SS_YY | NA | 15809 | 275 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 26314 | 222 | 72_22P | NA | NA | 88_CC | 26314 | 222 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 25323 | 274 | 72_22P | NA | SS_YY | NA | 25323 | 274 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 21684 | 305 | 72_22P | NA | NA | 88_CC | 21684 | 305 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 25931 | 453 | NA | 72_88P | SS_YY | NA | 25931 | 453 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 19928 | 462 | NA | 72_88P | SS_YY | NA | 19928 | 462 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 16925 | 12 | NA | 72_88P | NA | 88_CC | 16925 | 12 | NA | 72_88P | NA | 88_CC | 72_88P | 88_CC |
| 15927 | 8 | NA | 72_88P | SS_YY | NA | 15927 | 8 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 12930 | 35 | 72_22P | NA | NA | 88_CC | 12930 | 35 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 21702 | 178 | 72_22P | NA | SS_YY | NA | 21702 | 178 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
ScriptingRe: Question bout Table Merge, how to use JSL to merge multiple column data into one column?Correct the table as below:
| | | Original Table | | | | After Merge Table |
| ID_1 | ID_2 | WR@689700@WFI*HHC 56XYZ | WR@689799@WFI*HH 56XYZ W | WR@780990@UW*FFY 78XYZ | WR@780666@UW*FFY 78XYZ | --> | ID_1 | ID_2 | WR@689700@WFI*HHC 56XYZ | WR@689799@WFI*HH 56XYZ W | WR@780990@UW*FFY 78XYZ | WR@780666@UW*FFY 78XYZ | WR@1 | WR@2 |
| 22164 | 115 | 72_22P | NA | SS_YY | NA | --> | 22164 | 115 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 25335 | 33 | 72_22P | NA | NA | 88_CC | --> | 25335 | 33 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 24870 | 872 | 72_22P | NA | SS_YY | NA | --> | 24870 | 872 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 24861 | 591 | NA | 72_88P | SS_YY | NA | --> | 24861 | 591 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 12928 | 274 | 72_22P | NA | SS_YY | NA | --> | 12928 | 274 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 20927 | 818 | NA | 72_88P | SS_YY | NA | --> | 20927 | 818 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 22167 | 14 | 72_22P | NA | SS_YY | NA | --> | 22167 | 14 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 12930 | 120 | 72_22P | NA | NA | 88_CC | --> | 12930 | 120 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 28447 | 155 | 72_22P | NA | SS_YY | NA | --> | 28447 | 155 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 33380 | 138 | NA | 72_88P | NA | 88_CC | --> | 33380 | 138 | NA | 72_88P | NA | 88_CC | 72_88P | 88_CC |
| 18186 | 769 | 72_22P | NA | NA | 88_CC | --> | 18186 | 769 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 27557 | 91 | NA | 72_88P | NA | 88_CC | --> | 27557 | 91 | NA | 72_88P | NA | 88_CC | 72_88P | 88_CC |
| 14863 | 116 | 72_22P | NA | NA | 88_CC | --> | 14863 | 116 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 29928 | 204 | NA | 72_88P | SS_YY | NA | --> | 29928 | 204 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 15809 | 70 | 72_22P | NA | NA | 88_CC | --> | 15809 | 70 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 21701 | 269 | 72_22P | NA | SS_YY | NA | --> | 21701 | 269 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 27937 | 110 | NA | 72_88P | SS_YY | NA | --> | 27937 | 110 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 20136 | 454 | 72_22P | NA | SS_YY | NA | --> | 20136 | 454 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 27934 | 866 | NA | 72_88P | NA | 88_CC | --> | 27934 | 866 | NA | 72_88P | NA | 88_CC | 72_88P | 88_CC |
| 18186 | 759 | 72_22P | NA | SS_YY | NA | --> | 18186 | 759 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 27937 | 106 | NA | 72_88P | SS_YY | NA | --> | 27937 | 106 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 11921 | 170 | 72_22P | NA | SS_YY | NA | --> | 11921 | 170 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 15809 | 275 | 72_22P | NA | SS_YY | NA | --> | 15809 | 275 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 26314 | 222 | 72_22P | NA | NA | 88_CC | --> | 26314 | 222 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 25323 | 274 | 72_22P | NA | SS_YY | NA | --> | 25323 | 274 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
| 21684 | 305 | 72_22P | NA | NA | 88_CC | --> | 21684 | 305 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 25931 | 453 | NA | 72_88P | SS_YY | NA | --> | 25931 | 453 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 19928 | 462 | NA | 72_88P | SS_YY | NA | --> | 19928 | 462 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 16925 | 12 | NA | 72_88P | NA | 88_CC | --> | 16925 | 12 | NA | 72_88P | NA | 88_CC | 72_88P | 88_CC |
| 15927 | 8 | NA | 72_88P | SS_YY | NA | --> | 15927 | 8 | NA | 72_88P | SS_YY | NA | 72_88P | SS_YY |
| 12930 | 35 | 72_22P | NA | NA | 88_CC | --> | 12930 | 35 | 72_22P | NA | NA | 88_CC | 72_22P | 88_CC |
| 21702 | 178 | 72_22P | NA | SS_YY | NA | --> | 21702 | 178 | 72_22P | NA | SS_YY | NA | 72_22P | SS_YY |
Re: Question bout Table Merge, how to use JSL to merge multiple column data into one column?Here is one way to do this. It changes the values in place, so make sure you have saved the original copy of the table. All that needs to be done to get this to correct the entire table is to create the 2 comparison lists of column names(compList1 & compList2)
Names Default To Here( 1 );
dt = Current Data Table();
// Create the lists of the columns to be compaired
// compList1[1] will be compired with compList2[1] etc.
compList1 = {"WR@689700@WFI*HHC 56XYZ", "WR@780990@UW*FFY 78XYZ"};
compList2 = {"WR@689799@WFI*HH 56XYZ W", "WR@780666@UW*FFY 78XYZ"};
// Loop across the columns, finding which rows to be changed and
// then loop across those rows and change them
For( i = 1, i <= N Items( compList1 ), i++,
// Find which rows to change
theRows = dt << get rows where( As Column( dt, compList1[i] ) == "NA" & As Column( dt, compList2[i] ) != "NA" );
// Change the rows
For( k = 1, k <= N Rows( theRows ), k++,
Column( dt, compList1[i] )[theRows[k]] = Column( dt, compList2[i] )[theRows[k]]
);
);
// Delete the duplicate columns
dt << delete columns( compList2 );
