Add lookups from lookup tables

The page provides a guide on how to add lookups from lookup tables in Upsolver.

1. Click Add Lookup or in the Calculated Fields tab, under Lookups, click.

2. Under the Lookup Table tab, select a source for your lookup.

The lookup table you wish to use must be currently running. See: Lookup table data output if you have not yet created a lookup table.

3. Click Next.

4. Select a lookup table time offset; this enables you to sync your data.

When joining, the data source and the lookup table may not be completely in sync (there may be a lead or a lag).

An event in the data source may arrive before or after the corresponding event in the lookup table.

Current

Use the same time as the data.

Past

Set an interval in the past.

Future

Set an interval in the future.

Note: The output will only be written once this future interval has passed.

5. Check Use Full History to use all the data that was available when deploying this output for historical lookups.

When joining a stream and a lookup, generally you use the time field for the join. As you move forward with the stream, you join to the lookup based on the time in the lookup.

This applies to the past data too; if processing an output for the past 12 months, the time in the lookup is used to match to the stream. This will not work in cases such as joining a stream with a year of data to a static table which has just recently been dumped.

In this case in order to join this data, check Use Full History. The time of the original event is then disregarded; instead the current time is used to perform the join (e.g. it uses the latest state of the data).

6. Check Filter Missing to to filter out records that don't have a matching result from this lookup (like INNER JOIN in SQL).

7. Under Map Key Fields, map the lookup keys and the time field.

The field time is the time that the event was processed by Upsolver. You may choose a different field for synching purposes.

8. Select where to locate the calculated field (e.g. root, data) and name this field. This operation joins the input arrays using a cartesian product.

The fields are typically added at the root level, however on occasion you can opt to co‑locate a field with another element in the hierarchy.

9. Click Preview to review your calculated field before saving; a Field Content Samples panel will appear on the right.

10. To add columns to the preview, click Choose Columns and select the desired columns. Then click Update.

11. Click Save.

You can now find this lookup under Lookups in the sidebar or under the Calculated Fields tab.