跳转至

Lookup datasets（查找数据集）

Foundry datasets are imported into Fusion via lookup formulas. These formulas are similar to Microsoft's VLOOKUP function, but are used to bring in live data from a Foundry dataset, as opposed to a spreadsheet.

You can search for data and generate these formulas with the Find and use data panel - see Find and use data for more details. You can also write these formulas yourself and use them nested in other formulas.

The following lookup formulas exist:

  • lookup: Returns a set of values from a dataset column with optional filters. A single result is returned as a value, while multiple results are returned in an array. For example, =lookup(dataset_name, column_name, filter_column_1, filter_value_1, filter_column_2, filter_value_2).
  • lookup_array: Same as lookup except single results are returned in an array (of length 1).
  • lookup_distinct: Returns the distinct set of values from a dataset column, with optional filters.
  • lookup_dropdown: Returns a dropdown where the selectable values are the results of the lookup.
  • lookup_sorted: Returns a set of values sorted ascending or descending by a column of the dataset.
  • lookup_schema: Returns an array with the schema of a dataset.

You can find more details on these specific formulas in the function library.

Lookups (e.g. sum(lookup(...))) are limited to 2,000 results. If your workflow requires a larger number of elements, you should first perform the aggregations or pivots in Contour, then save the resulting table as a dataset that you can index in Fusion.

Any of the arguments in a lookup can be a cell reference from your spreadsheet. This allows you to create dynamic lookups which depend on user input, derived cells, or other lookups.

:::callout{theme="success" title="Tip"} To expand an array of results from a lookup, you can Shift+drag that cell down into individual cells. :::

:::callout{theme="success" title="Tip"} lookup calls with cell references can be dragged or copy/pasted to other parts of the spreadsheet for context specific results. :::

中文翻译

查找数据集

Foundry 数据集通过 lookup 公式导入 Fusion。这些公式类似于 Microsoft 的 VLOOKUP 函数，但用于引入 Foundry 数据集中的实时数据，而非电子表格数据。

您可以通过 查找并使用数据 面板搜索数据并生成这些公式——详见查找并使用数据。您也可以自行编写这些公式，并将其嵌套在其他公式中使用。

目前支持以下 lookup 公式：

  • lookup：从数据集的列中返回一组值，支持可选筛选条件。单个结果以值的形式返回，多个结果则以数组形式返回。例如：=lookup(dataset_name, column_name, filter_column_1, filter_value_1, filter_column_2, filter_value_2)。
  • lookup_array：与 lookup 功能相同，但单个结果以数组形式返回（数组长度为 1）。
  • lookup_distinct：从数据集的列中返回去重后的值集合，支持可选筛选条件。
  • lookup_dropdown：返回一个下拉菜单，其可选值为查找结果。
  • lookup_sorted：根据数据集的某一列按升序或降序返回一组值。
  • lookup_schema：返回包含数据集 schema 的数组。

您可以在函数库中找到这些特定公式的更多详细信息。

查找操作（例如 sum(lookup(...))）的结果限制为 2,000 条。如果您的工作流需要处理更多元素，应先在 Contour 中执行聚合或透视操作，然后将生成的表格保存为可在 Fusion 中索引的数据集。

lookup 中的任何参数都可以引用电子表格中的单元格。这使您可以创建动态查找，其内容取决于用户输入、派生单元格或其他查找结果。

:::callout{theme="success" title="提示"} 要展开 lookup 返回的数组结果，您可以按住 Shift 键将该单元格向下拖动到各个单元格中。 :::

:::callout{theme="success" title="提示"} 包含单元格引用的 lookup 调用可以拖拽或复制/粘贴到电子表格的其他位置，以获取特定上下文的结果。 :::