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 TableID_1ID_2WR@689700@WFI*HHC 56XYZWR@689799@WFI*HH 56XYZ WWR@780990@UW*FFY 78XYZWR@780666@UW*FFY 78XYZ-->ID_1ID_2WR@689700@WFI*HHC 56XYZWR@689799@WFI*HH 56XYZ WWR@780990@UW*FFY 78XYZWR@780666@UW*FFY 78XYZWR@1WR@22216411572_22PNASS_YYNA2216411572_22PNASS_YYNA72_22PSS_YY253353372_22PNANA88_CC253353372_22PNANA88_CC72_22P88_CC2487087272_22PNASS_YYNA2487087272_22PNASS_YYNA72_22PSS_YY24861591NA72_88PSS_YYNA24861591NA72_88PSS_YYNA72_88PSS_YY1292827472_22PNASS_YYNA1292827472_22PNASS_YYNA72_22PSS_YY20927818NA72_88PSS_YYNA20927818NA72_88PSS_YYNA72_88PSS_YY221671472_22PNASS_YYNA221671472_22PNASS_YYNA72_22PSS_YY1293012072_22PNANA88_CC1293012072_22PNANA88_CC72_22P88_CC2844715572_22PNASS_YYNA2844715572_22PNASS_YYNA72_22PSS_YY33380138NA72_88PNA88_CC33380138NA72_88PNA88_CC72_88P88_CC1818676972_22PNANA88_CC1818676972_22PNANA88_CC72_22P88_CC2755791NA72_88PNA88_CC2755791NA72_88PNA88_CC72_88P88_CC1486311672_22PNANA88_CC1486311672_22PNANA88_CC72_22P88_CC29928204NA72_88PSS_YYNA29928204NA72_88PSS_YYNA72_88PSS_YY158097072_22PNANA88_CC158097072_22PNANA88_CC72_22P88_CC2170126972_22PNASS_YYNA2170126972_22PNASS_YYNA72_22PSS_YY27937110NA72_88PSS_YYNA27937110NA72_88PSS_YYNA72_88PSS_YY2013645472_22PNASS_YYNA2013645472_22PNASS_YYNA72_22PSS_YY27934866NA72_88PNA88_CC27934866NA72_88PNA88_CC72_88P88_CC1818675972_22PNASS_YYNA1818675972_22PNASS_YYNA72_22PSS_YY27937106NA72_88PSS_YYNA27937106NA72_88PSS_YYNA72_88PSS_YY1192117072_22PNASS_YYNA1192117072_22PNASS_YYNA72_22PSS_YY1580927572_22PNASS_YYNA1580927572_22PNASS_YYNA72_22PSS_YY2631422272_22PNANA88_CC2631422272_22PNANA88_CC72_22P88_CC2532327472_22PNASS_YYNA2532327472_22PNASS_YYNA72_22PSS_YY2168430572_22PNANA88_CC2168430572_22PNANA88_CC72_22P88_CC25931453NA72_88PSS_YYNA25931453NA72_88PSS_YYNA72_88PSS_YY19928462NA72_88PSS_YYNA19928462NA72_88PSS_YYNA72_88PSS_YY1692512NA72_88PNA88_CC1692512NA72_88PNA88_CC72_88P88_CC159278NA72_88PSS_YYNA159278NA72_88PSS_YYNA72_88PSS_YY129303572_22PNANA88_CC129303572_22PNANA88_CC72_22P88_CC2170217872_22PNASS_YYNA2170217872_22PNASS_YYNA72_22PSS_YY

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Question bout Table Merge, how to use JSL to merge multiple column data into one column?

qspringleaf

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

SS_YY

22164

115

72_22P

SS_YY

72_22P

SS_YY

25335

72_22P

88_CC

25335

72_22P

88_CC

72_22P

88_CC

24870

872

72_22P

SS_YY

24870

872

72_22P

SS_YY

72_22P

SS_YY

24861

591

72_88P

SS_YY

24861

591

72_88P

SS_YY

72_88P

SS_YY

12928

274

72_22P

SS_YY

12928

274

72_22P

SS_YY

72_22P

SS_YY

20927

818

72_88P

SS_YY

20927

818

72_88P

SS_YY

72_88P

SS_YY

22167

72_22P

SS_YY

22167

72_22P

SS_YY

72_22P

SS_YY

12930

120

72_22P

88_CC

12930

120

72_22P

88_CC

72_22P

88_CC

28447

155

72_22P

SS_YY

28447

155

72_22P

SS_YY

72_22P

SS_YY

33380

138

72_88P

88_CC

33380

138

72_88P

88_CC

72_88P

88_CC

18186

769

72_22P

88_CC

18186

769

72_22P

88_CC

72_22P

88_CC

27557

72_88P

88_CC

27557

72_88P

88_CC

72_88P

88_CC

14863

116

72_22P

88_CC

14863

116

72_22P

88_CC

72_22P

88_CC

29928

204

72_88P

SS_YY

29928

204

72_88P

SS_YY

72_88P

SS_YY

15809

72_22P

88_CC

15809

72_22P

88_CC

72_22P

88_CC

21701

269

72_22P

SS_YY

21701

269

72_22P

SS_YY

72_22P

SS_YY

27937

110

72_88P

SS_YY

27937

110

72_88P

SS_YY

72_88P

SS_YY

20136

454

72_22P

SS_YY

20136

454

72_22P

SS_YY

72_22P

SS_YY

27934

866

72_88P

88_CC

27934

866

72_88P

88_CC

72_88P

88_CC

18186

759

72_22P

SS_YY

18186

759

72_22P

SS_YY

72_22P

SS_YY

27937

106

72_88P

SS_YY

27937

106

72_88P

SS_YY

72_88P

SS_YY

11921

170

72_22P

SS_YY

11921

170

72_22P

SS_YY

72_22P

SS_YY

15809

275

72_22P

SS_YY

15809

275

72_22P

SS_YY

72_22P

SS_YY

26314

222

72_22P

88_CC

26314

222

72_22P

88_CC

72_22P

88_CC

25323

274

72_22P

SS_YY

25323

274

72_22P

SS_YY

72_22P

SS_YY

21684

305

72_22P

88_CC

21684

305

72_22P

88_CC

72_22P

88_CC

25931

453

72_88P

SS_YY

25931

453

72_88P

SS_YY

72_88P

SS_YY

19928

462

72_88P

SS_YY

19928

462

72_88P

SS_YY

72_88P

SS_YY

16925

72_88P

88_CC

16925

72_88P

88_CC

72_88P

88_CC

15927

72_88P

SS_YY

15927

72_88P

SS_YY

72_88P

SS_YY

12930

72_22P

88_CC

12930

72_22P

88_CC

72_22P

88_CC

21702

178

72_22P

SS_YY

21702

178

72_22P

SS_YY

72_22P

SS_YY

Accepted answers

txnelson

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

All comments

qspringleaf

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

SS_YY

-->

22164

115

72_22P

SS_YY

72_22P

SS_YY

25335

72_22P

88_CC

-->

25335

72_22P

88_CC

72_22P

88_CC

24870

872

72_22P

SS_YY

-->

24870

872

72_22P

SS_YY

72_22P

SS_YY

24861

591

72_88P

SS_YY

-->

24861

591

72_88P

SS_YY

72_88P

SS_YY

12928

274

72_22P

SS_YY

-->

12928

274

72_22P

SS_YY

72_22P

SS_YY

20927

818

72_88P

SS_YY

-->

20927

818

72_88P

SS_YY

72_88P

SS_YY

22167

72_22P

SS_YY

-->

22167

72_22P

SS_YY

72_22P

SS_YY

12930

120

72_22P

88_CC

-->

12930

120

72_22P

88_CC

72_22P

88_CC

28447

155

72_22P

SS_YY

-->

28447

155

72_22P

SS_YY

72_22P

SS_YY

33380

138

72_88P

88_CC

-->

33380

138

72_88P

88_CC

72_88P

88_CC

18186

769

72_22P

88_CC

-->

18186

769

72_22P

88_CC

72_22P

88_CC

27557

72_88P

88_CC

-->

27557

72_88P

88_CC

72_88P

88_CC

14863

116

72_22P

88_CC

-->

14863

116

72_22P

88_CC

72_22P

88_CC

29928

204

72_88P

SS_YY

-->

29928

204

72_88P

SS_YY

72_88P

SS_YY

15809

72_22P

88_CC

-->

15809

72_22P

88_CC

72_22P

88_CC

21701

269

72_22P

SS_YY

-->

21701

269

72_22P

SS_YY

72_22P

SS_YY

27937

110

72_88P

SS_YY

-->

27937

110

72_88P

SS_YY

72_88P

SS_YY

20136

454

72_22P

SS_YY

-->

20136

454

72_22P

SS_YY

72_22P

SS_YY

27934

866

72_88P

88_CC

-->

27934

866

72_88P

88_CC

72_88P

88_CC

18186

759

72_22P

SS_YY

-->

18186

759

72_22P

SS_YY

72_22P

SS_YY

27937

106

72_88P

SS_YY

-->

27937

106

72_88P

SS_YY

72_88P

SS_YY

11921

170

72_22P

SS_YY

-->

11921

170

72_22P

SS_YY

72_22P

SS_YY

15809

275

72_22P

SS_YY

-->

15809

275

72_22P

SS_YY

72_22P

SS_YY

26314

222

72_22P

88_CC

-->

26314

222

72_22P

88_CC

72_22P

88_CC

25323

274

72_22P

SS_YY

-->

25323

274

72_22P

SS_YY

72_22P

SS_YY

21684

305

72_22P

88_CC

-->

21684

305

72_22P

88_CC

72_22P

88_CC

25931

453

72_88P

SS_YY

-->

25931

453

72_88P

SS_YY

72_88P

SS_YY

19928

462

72_88P

SS_YY

-->

19928

462

72_88P

SS_YY

72_88P

SS_YY

16925

72_88P

88_CC

-->

16925

72_88P

88_CC

72_88P

88_CC

15927

72_88P

SS_YY

-->

15927

72_88P

SS_YY

72_88P

SS_YY

12930

72_22P

88_CC

-->

12930

72_22P

88_CC

72_22P

88_CC

21702

178

72_22P

SS_YY

-->

21702

178

72_22P

SS_YY

72_22P

SS_YY

txnelson

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

qspringleaf

thanks for your suggestion, but considering >20 columns in the table, and how to save effort to create compList1 and compList2, is it possible to use JSL to get all column names and group them, because sometimes we not even know need put the column name to compList1 or compList2?

txnelson

If there is a rule as to what columns to compare, I am sure JSL can build the lists, but.....I don't know the rule(s)