Amazon QuickSight – Mapping & Joining Fields
While working with multiple datasets in Amazon QuickSight, streamlining the process of mapping fields or joining tables during data preparation can be beneficial. You should already be ensuring that your fields possess the correct data type and suitable field names. If you are aware of the datasets that will be used together, you can take a few additional steps to simplify your work in the future.
Mapping
Amazon QuickSight can automatically map fields between datasets within the same analysis. To facilitate this process, consider the following tips that can help ensure accurate automatic field mapping between datasets, such as when creating a filter action across datasets:
- Matching field names – Field names must be identical, with no variations in case, spacing, or punctuation. Rename fields that represent the same data to enable precise automatic mapping.
- Matching data types – Fields must share the same data type for automatic mapping. Alter data types during data preparation, which also allows you to identify and filter out any data with incorrect data types.
- Using calculated fields – Create matching fields using calculated fields and assign them the appropriate name and data type for automatic mapping.
Joining
You can establish joins between data from various data sources, such as files or databases. To simplify the process of joining data from different files or sources, consider the following tips:
- Consistent field names – Joining fields is more straightforward when it’s evident which fields should match, for example, Order ID and order-id. However, if one represents a work order and the other a purchase order, they likely contain different data. Ensure that the files and tables you want to join have clear field names that accurately describe their contents.
- Matching data types – Before joining, fields must share the same data type. Verify that the files and tables you plan to join have matching data types in their respective join fields. Note that you cannot use calculated fields for joins or join two existing datasets. Instead, create the joined dataset by directly accessing the source data.