collectColumns

[ 1 Operator usage in easy mode ] [ 2 Usage details ] [ 3 Example ]

Operator usage in easy mode

Click + on the parent node.
Enter the Collect Columns operator in the search field and select the operator from the Results to open the operator form.
In the Tables drop-down, enter or select a table or list of tables.
In the Tables Column, click Add More to add the list of table columns to collect.
In the Join Column, enter the column names to join the table.
Click Run to view the result.
Click Save to add the operator to the playbook.
Click Cancel to discard the operator form.

Usage details

Collects columns from multiple tables that is provided by user. All tables must contain key column to join.

LQL Command

collectColumns(tableList, tableColumnsList, columnNameToJoin)

Input Params:
tableList: list of tables, e.g. [table1, table2, table3 ...]
tableColumnsList: list of columns prepended by table name e.g. ["table1.col1", "table2.col2", "table3.f3" ...]
columnNameToJoin: column name to outer join tables

Output:
result table is outer join of all tables by columnNameToJoin parameter that user specified, and select tableColumnsList columns that user have provided, "." will be replaced with "__"

Example

Input
table1, table2, table3

user_id	scorer1

user_id	scorer1
1	1.0
2	2.0
4	4.0

table2

user_id	scorer2

user_id	scorer2
1	9.0
3	9.0
5	8.0

table3

scorer

user_id	scorer3	scorerName

user_id	scorer3	scorerName
1	5.0	virusTotal
2	9.0	virusTotal
3	7.0	virusTotal

LQL command

collectColumns([table1, table2, table3], ["table1.score1", "table2.score2", "table3.score3"], "user_id")

Output

user_id	table1__scorer1	table2__scorer2	table3__scorer3

user_id	table1__scorer1	table2__scorer2	table3__scorer3
1	1.0	9.0	5.0
2	2.0		9.0
3		9.0	7.0
4	4.0
5		8.0

Additional Note:
for following LQL
collectColumns([t1, t2, t3], ["t1.col1", "t2.col2", "t3.col3"], "id")
under the hood this operator executes following sql logic:

SQL

select 
  t1.col1 as t1__col1, 
  t2.col2 as t2__col2,
  t3.col3 as t3__col3
from t1 
full outer join t2 on t1.id = t2.id
full outer join t3 on t1.id = t3.id