If you are a regular user of the vlookup function in Microsoft Excel, the Table Lookup transform should feel very familiar. It’s used to perform essentially the same function. Unlike the Microsoft Excel version, the PlaidCloud Analyze Table Lookup transform offers greater flexibility, especially allowing for matching on and returning multiple columns.
To establish the source and target, first select the data table to be exported from the Source Table dropdown menu. Next, select the target file path from PlaidCloud Document using the dropdown menu to select the appropriate account before navigating to the actual directory in the section immediately below. Finally, provide the target file with a descriptive name.
Providing a file extension is advised, but not required by Analyze. The data table will be exported into the appropriate file format with or without an extension.
Select the Lookup Table from the dropdown list. This list includes all Project and Workflow data tables.
Any valid Python expression is acceptable to subset the data. Please see the following section for more details and examples: Expressions
To limit the data, simply check the Apply Row Slicer box and then specify the following:
The Table Data Selection tab is used to map columns from the source data table to the target data table. All source columns on the left side of the window are automatically mapped to the target data table depicted on the right side of the window. Using the Inspect Source menu button, there are a few additional ways to map columns from source to target:
In addition to each of these options, each choice offers the ability to preview the source data.
If the source and target column options aren’t enough, other columns can be added into the target data table in several different ways:
Selecting Propagate All may effectively create a duplicate of every column. Analyze does not check to see
if the columns are already mapped. Make sure duplicate column names do not exist.
To delete columns from the target data table, select the desired column(s), then right click and select Delete.
To rearrange columns in the target data table, select the desired column(s), then right click and select Move to Top, Move Up, Move Down, or Move to Bottom.
To return only distinct options, select the Distinct menu option. This will toggle a set of checkboxes for each column in the source. Simply check any box next to the corresponding column to return distinct results only.
When the target data table contains only a subset of the source data table, only select the check box next to the columns which are to be included in the target data table. Selecting all checkboxes could provide output that does not appear to be distinct.
To aggregate results, select the Summarize menu option. This will toggle a set of drop down boxes for each column in the target data table. The following summarization options are available:
For more aggregation details, see the Analyze overview page [here](/docs/analyze/#aggregation).
To allow for maximum flexibility, data filters are available on the source data and the target data. For larger data sets, it can be especially beneficial to filter out rows on the source so the remaining operations are performed on a smaller data set.
Any valid Python expression is acceptable to subset the data. Please see Expressions for more details and examples.
Compound filters must have individual elements wrapped in parentheses. For example, if filtering for Temperature and Humidity, a valid filter would look like this:
To report duplicates, select the Report Duplicates in Table checkbox and then specify an output table which will contain all of the duplicate records.
This will not remove the duplicate items from the target data table. To remove duplicate items, use the Distinct menu options as specified in the [Table Data Selection](../transforms/common_features#table-data-selection) section.
Any valid Python expression is acceptable to subset the data. Please see Expressions for more details and examples
Example code here
To limit the data, check the Apply Row Slicer box and then specify the following:
To view the columns available in the lookup table, simply select the Inspect Source button and one of its options to Populate Lookup Source Mapping Table. If desired, options are available to preview the data in the lookup table.
By default, the lookup table returns distinct values for all columns. Since a lookup returns the first matching value, it is not necessary to leave duplicate values in the lookup table. If desired, this option can be turned off by deselecting the Distinct checkbox for individual columns.
The Lookup Relationship Map is used to specify all columns used for matching. By using the Guess button, Analyze will populate the relationship map with all columns from the source data table which have a matching name in the lookup data table.
Additionally, these values can be entered manually by right-clicking the mouse button and selecting either Insert Row or Append Row to insert a row prior to the selected row or to insert a row at the bottom of the list, respectively. In a similar manner, items can be removed by right-clicking and selecting the Delete option.
The Field Value Map is used to append columns of data onto the target data table based upon matching values found. By default, using the Guess button will add all columns which were not used in the Lookup Relationship Map section. If not all columns are desired, items can be removed by right-clicking and selecting the Delete option.
Additionally, there are options available to rename the mapped columns simply by clicking into the Update/Add Data Table Field selection and adjusting the name as desired. Column order can also be adjusted as desired by right-clicking on an item and using the Move to Top, Move Up, Move Down, or Move to Bottom options.
In this example, the modeler needs information from the product dimension table to make sense of the order fact table. As such, the Import Order Fact table is selected as the Source Table. The Import Product Dim table contains the desired lookup information, so it’s selected as the Lookup Table Source. Although available, no filters are applied to the lookup data table (nor any other data tables, for that matter).
In the Table Data Selection section, all columns are mapped from the source data table to the target data table.
No Data Filters are applied to either source or target data.
Lastly, the source data table is matched to the lookup data table using the Product_ID field found in each table. Only the Product_Description and Unit_Cost columns are appended to the target data table, with Unit_Cost being renamed to Retail_Unit_Cost in the process.
In the resulting target data table, the Product_Description and Retail_Unit_Cost columns have been added, based on matching values in the Product_ID column.
For an example showing how to standardize values with a lookup data table, please see the Standardize Team Names & Finalize Human Polls section of the BCS Demo.