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