collectColumns

Collect columns from multiple steps

Operator Usage in Easy Mode

  1. Click + on the parent node.
  2. Enter the Collect Columns operator in the search field and select the operator from the Results to open the operator form.
  3. In the Tables drop-down, enter or select a table or list of tables.
  4. In the Tables Column, click Add More to add the list of table columns to collect.
  5. In the Join Column, enter the column names to join the table.
  6. Click Run to view the result.
  7. Click Save to add the operator to the playbook.
  8. 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.

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
11.0
22.0
44.0

table2

user_id scorer2
19.0
39.0
58.0

table3

scorer
user_id scorer3 scorerName
15.0virusTotal
29.0virusTotal
37.0virusTotal
collectColumns([table1, table2, table3], ["table1.score1", "table2.score2", "table3.score3"], "user_id")

Output

user_id table1__scorer1 table2__scorer2 table3__scorer3
11.09.05.0
22.09.0
39.07.0
44.0
58.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:

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

© 2017-2021 LogicHub®. All Rights Reserved.