2.2.3.51 wjoincols

Menu Information

Restructure: Join Two Sheets by Column

Brief Information

Join multiple worksheets by matching columns

Additional Information

Minimum Origin Version Required: 2022

Command Line Usage

1. wjoincols irng:=([Book3]Sheet1,[Book1]Sheet1) settings.cols:="<All>||A|B" settings.unmatch:="0|1" settings.multiple:="0|1" settings.conditions:="w1.A=w2.A" settings.sort:=3 settings.sortsheet:=1 settings.sortcol:="w1.A";

2. wjoincols -r 2 irng:=([Book2]Data2,[Book1]Data1) settings.cols:="ID1|ID2||ID1|ID10" settings.conditions:="w1.ID1=w2.ID1" settings.unmatch:="1|1" settings.multiple:="1|1" settings.mergeby:=5 settings.merge:=0;

3. wjoincols irng:=([Book2]Sheet1,[Book1]Sheet1) settings.cols:="<All>||<All>" settings.conditions:="w1.A=w2.A|w1.B=w2.B" settings.unmatch:="0|1" settings.multiple:="0|1" settings.sortsheet:=1;

4. wjoincols irng:=([Book2]Sheet1,[Book1]Sheet1) query:="w1 left join w2 on w1.A=w2.A";

5. wjoincols -r 2 irng:=([Book1]Sheet1,[Book2]Sheet1) query:="w1 inner join w2 on w1.A=w2.A and w1.B=w2.B order by match asc merge multiples by first row";

Variables

Display
Name
Variable
Name
I/O
and
Type
Default
Value
Description
Input irng

Input

Range

<active>
Specify the input worksheet range.

The syntax is: ([BookName1]SheetName1!, [BookName2]SheetName2!).

Settings settings

Input

TreeNode

<unassigned>
The treenode of control settings. See Details of Settings TreeNode section for details.
query

Input

string

Specify combination options such as e.g. matching condition, multiple matched cells, non-match values.etc in SQL language. See Samples of SQL query section for details.
Output Worksheet ow

Output

Worksheet

<new>
Specify the output range, see syntax here.

Description

This X-Function is used to combine two worksheets into one. The combination will be determined by matching columns in each input worksheet. Several options are provided to determine how to treat special cases when combining (e.g. multiple matched cells, non-match values, missing values.etc). Refer to the Origin Help page about this tool to learn the details of the functionality of this tool.

When executed via LabTalk script, this X-Function also supports using SQL language to specify matching condition and combining options.

Details of Settings TreeNode

The settings tree specifies all setting options for the addtool_rise_time X-Function.

Syntax: settings.Treenode:=<value>

Example: settings.sort = 2

Treenode Label Type Default Description
cols Input string Specify the columns to join. Sheets are separated by "||" and columns within same sheet separated by "|". For example,
settings.cols:="<All>||w2-A|w2-B"
means all columns in the first sheet and two columns with LongName of "w2-A" and "w2-B" are selected.

Wjoincols settings cols.png

unmatch Drop Non-matches Input string Specify whether to drop the values that do not have a match. w1 and w2 are controlled respectively and separated by "|", for example, 0|1.
  • 0 = do not drop and fill in other columns for non-match values with missing values,
  • 1 = drop non-matches
multiple Drop Multiples Input string If there are multiple matched cells for one value, this control is used to specify whether to merge the duplicated rows by statistics value specified by mergeby and drop the replica. w1 and w2 are controlled respectively and separated by "|", for example, 0|1.

0 = do not drop, 1 = drop.

conditions Matching Columns INput string Specify the matching column condition(s) to join the worksheets. All values in the matching column(s) will be compared and the matched rows will be combined as the same row in the result worksheet.

The full syntax is:
w1.ColumnLongName/ShortName1 = w2.ColumnLongName/ShortName2
For examlpe, w1.A=w2.A

mergeby Merge Multiples by input int 0 Available when multiple = 1. Specify the statistics value to replace the multiple.

0 = First Row, 1 = Last Row, 2 = Max, 3 = Min, 4 = Average, 5 = Sum.

combine Match with All Combinations input int 1 Determine whether to show all possible combinations in the joined worksheet.
0 = do not show all combinations and fill in other columns without combinations with missing values,
1 = show all combinations
merge Merge Matched Columns as One input int 1 Specify whether to keep only one matched column in the joined worksheet.
0 = Do not keep. In this case, there will be multiple matched columns in the joined sheet,
1 = Keep only one matched column in the joined worksheet. In this case, only one matching column will be shown in the joined worksheet as the first column and the data columns will be arranged side by side.
missing Consider Missing Value input int 0 Specify whether to ignore rows with missing values in the matching columns.
0 = if there are missing values in matching column, the whole data row will be ignored in the result sheet,
1 = the missing values in the matching column(s) will be treated as a separate group.
sort Sort Output input int 2 Control the order of mathed values in the joined worksheet.
0 = Matching Columns -- Ascending
1 = Matching Columns -- Descending
2 = Preserve sheet order: sort by order of matched values found in the sheet specified in sortsheet.
3 = by Specified Columns -- Ascending: sort the joined worksheet by column specified in sortcol ascendingly.
4 = by Specified Columns -- Descending: sort the joined worksheet by column specified in sortcol descendingly.
sortsheet Sheet input int 0 Available when sort = 2. Specify the worksheet index to sort by. 0 = w1, 1 = w2.
sortcol Column input string Available when sort = 3/4. Specify the column to sort by.

Syntax is w1/w2.ColumnLongName/ShortName. For example, "w1.ID1".

id Dataset Identifier input int 0 Specify source dataset indentifier.

0 = None, 1 = Range, 2 = Book Name, 3 = Sheet Name, 4 = Use index constructed in index to identify dadtaset source, 5 = Show sheet label of source worksheet.

index Index input string <auto> Available when id = 4. Specify the index of the identifier.

Format is start:increment. For example, 1:2 will indentify columns from w1 as "1" and w2 as "3".

Samples of SQL query

1. Drop Non-matches

w1 Left Join w2 on //Left Join could be Right Join/Inner Join/Full Outer Join
       w1.A=w2.A
Left Join Right Join Inner Join Full Outer Join
Left Join.png Right Join.png Inner Join.png Full Outer Join.png

2. Multiple Matching Columns

w1 Inner Join w2 on //Inner Join could be Left Join/Right Join/Full Outer Join
       w1.ID=W2.UserID
       and w1.A=w2.A // and could be or

3. Order by Specified Column

w1 Full Outer Join w2 on //Full Outer Join could be Left Join/Right Join/Inner Join
       w1.A=w2.A
Order by w1.C asc //w1.C could be w1/w2/match, asc could be desc

4. Drop Multiples

w1 Right Join w2 on //Right Join could be Left Join/Inner Join/Full Outer Join
       w1.A=w2.A
w1 Merge multiples by Average //Average could be First/Last/Min/Max/. In this case, w2 do not drop multiples

Related X-Functions

wjoinbycol, wAppend, wjoinbylabel