# Preparing data fields for analysis in Amazon Quick Sight Preparing data fields Before you start analyzing and visualizing your data, you can prepare the fields (columns) in your dataset for analysis. You can edit field names and descriptions, change the data type for fields, set up drill-down hierarchies for fields, and more. Use the following topics to prepare fields in your dataset. **Topics** + [ # Editing field names and descriptions ](changing-a-field-name.md) + [ # Setting fields as a dimensions or measures ](setting-dimension-or-measure.md) + [ # Changing a field data type ](changing-a-field-data-type.md) + [ # Adding drill-downs to visual data in Quick Sight ](adding-drill-downs.md) + [ # Selecting fields ](selecting-fields.md) + [ # Organizing fields into folders in Amazon QuickSight ](organizing-fields-folder.md) + [ # Mapping and joining fields ](mapping-and-joining-fields.md) # Editing field names and descriptions You can change any field name and description from what is provided by the data source. If you change the name of a field used in a calculated field, make sure also to change it in the calculated field function. Otherwise, the function fails. **To change a field name or description** 1. In the **Fields** pane of the data prep page, choose the three-dot icon on the field that you want to change. Then choose **Edit name & description**. 1. Enter the new name or description that you want to change, and choose **Apply**. You can also change the name and description of a field on the data prep page. To do this, select the column header of the field that you want to change in the **Dataset** table in that page's lower half. Then make any changes there. # Setting fields as a dimensions or measures In the **Field list** pane, dimension fields have blue icons and measure fields have green icons. *Dimensions* are text or date fields that can be items, like products, or attributes that are related to measures. You can use dimensions to partition these items or attributes, like sales date for sales figures. *Measures* are numeric values that you use for measurement, comparison, and aggregation. In some cases, Quick Sight interprets a field as a measure that you want to use it as a dimension (or the other way around). If so, you can change the setting for that field. Changing a field's measure or dimension setting changes it for all visuals in the analysis that use that dataset. However, it doesn't change it in the dataset itself. ## Changing a field's dimension or measure setting Use the following procedure to change a field's dimension or measure setting **To change a field's dimension or measure setting** 1. In the **Field list** pane, hover over the field that you want to change. 1. Choose the selector icon to the right of the field name, and then choose **Convert to dimension** or **Convert to measure** as appropriate. # Changing a field data type When Quick Sight retrieves data, it assigns each field a data type based on the data in the field. The possible data types are as follows: + Date – The date data type is used for date data in a supported format. For information about the date formats Quick Sight supports, see [Data source quotas](data-source-limits.md). + Decimal – The decimal data type is used for numeric data that requires one or more decimal places of precision, for example 18.23. The decimal data type supports values with up to four decimal places to the right of the decimal point. Values that have a higher scale than this are truncated to the fourth decimal place in two cases. One is when these values are displayed in data preparation or analyses, and one is when these values are imported into Quick Sight. For example, 13.00049 is truncated to 13.0004. + Geospatial – The geospatial data type is used for geospatial data, for example longitude and latitude, or cities and countries. + Integer – The int data type is used for numeric data that only contains integers, for example 39. + String – The string data type is used for nondate alphanumeric data. Quick Sight reads a small sample of rows in the column to determine the data type. The data type that occurs most in the small sample size is the suggested type. In some cases, there might be blank values (treated as strings by Quick Sight) in a column that contains mostly numbers. In these cases, it might be that the String data type is the most frequent type in the sample set of rows. You can manually modify the data type of the column to make it integer. Use the following procedures to learn how. ## Changing a field data type during data prep Changing type during data prep During data preparation, you can change the data type of any field from the data source. On the **Change data type** menu, you can change calculated fields that don't include aggregations to geospatial types. You can make other changes to the data type of a calculated field by modifying its expression directly. Quick Sight converts the field data according to the data type that you choose. Rows that contain data that is incompatible with that data type are skipped. For example, suppose that you convert the following field from String to Integer. ``` 10020 36803 14267a 98457 78216b ``` All records containing alphabetic characters in that field are skipped, as shown following. ``` 10020 36803 98457 ``` If you have a database dataset with fields whose data types aren't supported by Quick Sight, use a SQL query during data preparation. Then use `CAST` or `CONVERT` commands (depending on what is supported by the source database) to change the field data types. For more information about adding a SQL query during data preparation, see [Using SQL to customize data](adding-a-SQL-query.md). For more information about how different source data types are interpreted by Quick Sight, see [Supported data types from external data sources](supported-data-types-and-values.md#supported-data-types). You might have numeric fields that act as dimensions rather than metrics, for example ZIP codes and most ID numbers. In these cases, it's helpful to give them a string data type during data preparation. Doing this lets Quick Sight understand that they are not useful for performing mathematical calculations and can only be aggregated with the `Count` function. For more information about how Quick Sight uses dimensions and measures, see [Setting fields as a dimensions or measures](setting-dimension-or-measure.md). In [SPICE](spice.md), numbers converted from numeric into an integer are truncated by default. If you want to round your numbers instead, you can create a calculated field using the [`round`](round-function.md) function. To see whether numbers are rounded or truncated before they are ingested into SPICE, check your database engine. **To change a field data type during data prep** 1. From the Quick Sight homepage, choose **Data** at left. In the **Data** tab, choose the dataset that you want, and then choose **Edit dataset**. 1. In the data preview pane, choose the data type icon under the field you want to change. 1. Choose the target data type. Only data types other than the one currently in use are listed. ## Changing a field data type in an analysis Changing type in an analysis You can use the **Field list** pane, visual field wells, or on-visual editors to change numeric field data types within the context of an analysis. Numeric fields default to displaying as numbers, but you can choose to have them display as currency or as a percentage instead. You can't change the data types for string or date fields. Changing a field's data type in an analysis changes it for all visuals in the analysis that use that dataset. However, it doesn't change it in the dataset itself. **Note** If you are working in a pivot table visual, applying a table calculation changes the data type of the cell values in some cases. This type of change occurs if the data type doesn't make sense with the applied calculation. For example, suppose that you apply the `Rank` function to a numeric field that you modified to use a currency data type. In this case, the cell values display as numbers rather than currency. Similarly, if you apply the `Percent difference` function instead, the cell values display as percentages rather than currency. **To change a field's data type** 1. Choose one of the following options: + In the **Field list** pane, hover over the numeric field that you want to change. Then choose the selector icon to the right of the field name. + On any visual that contains an on-visual editor associated with the numeric field that you want to change, choose that on-visual editor. + Expand the **Field wells** pane, and then choose the field well associated with the numeric field that you want to change. 1. Choose **Show as**, and then choose **Number**, **Currency**, or **Percent**. # Adding drill-downs to visual data in Quick Sight Adding drill-downs All visual types except pivot tables offer the ability to create a hierarchy of fields for a visual element. The hierarchy lets you drill down to see data at different levels of the hierarchy. For example, you can associate the country, state, and city fields with the x-axis on a bar chart. Then, you can drill down or up to see data at each of those levels. As you drill down each level, the data displayed is refined by the value in the field you drill down on. For example, if you drill down on the state of California, you see data on all of the cities in California. The field wells you can use to create drill-downs varies by visual type. Refer to the topic on each visual type to learn more about its drill-down support. Drill-down functionality is added automatically for dates when you associate a date field with the drill-down field well of a visual. In this case, you can always drill up and down through the levels of date granularity. Drill-down functionality is also added automatically for geospatial groupings, after you define these in the dataset. Use the following table to identify the field wells/on-visual editors that support drill-down for each visual type. **** | Visual type | Field well or on-visual editor | | --- | --- | | Bar charts (all horizontal) | Y axis and Group/Color | | Bar charts (all vertical) | X axis and Group/Color | | Combo charts (all) | X axis and Group/Color | | Geospatial charts | Geospatial and Color | | Heat map | Rows and Columns | | KPIs | Trend Group | | Line charts (all) | X axis and Color | | Pie chart | Group/Color | | Scatter plot | Group/Color | | Tree map | Group by | **Important** Drill-downs are not suppoted for tables or pivot tables. ## Adding a drill-down Use the following procedure to add drill-down levels to a visual. **To add drill-down levels to a visual** 1. On the analysis page, choose the visual that you want to add drill-downs to. 1. Drag a field item into a **Field well**. 1. If your dataset has a defined hierarchy, you can drag the entire hierarchy into the field well as one. An example is geospatial or coordinate data. In this case, you don't need to follow the remaining steps. If you don't have a predefined hierarchy, you can create one in your analysis, as described in the remaining steps. 1. Drag a field that you want to use in the drill-down hierarchy to an appropriate field well, depending on the visual type. Make sure that the label for the dragged field says **Add drill-down layer**. Position the dragged field above or below the existing field based on where you want it to be in the hierarchy you're creating. 1. Continue until you have added all of the levels of hierarchy that you want. To remove a field from the hierarchy, choose the field, and then choose **Remove**. 1. To drill down or up to see data at a different level of the hierarchy, choose an element on the visual (like a line or bar), and then choose **Drill down to ** or **Drill up to **. In this example, from the `car-make` level you can drill down to `car-model` to see data at that level. If you drill down to `car-model` from the **Ford** `car-make`, you see only `car-model`s in that car-make. After you drill down to the `car-model` level, you can then drill down further to see `make-year` data, or go back up to `car-make`. If you drill down to `make-year` from the bar representing **Ranger**, you see only years for that model of car. # Selecting fields When you prepare data, you can select one or more fields to perform an action on them, such as excluding them or adding them to a folder. To select one or more fields in the data preparation pane, click or tap the field or fields in the **Fields** pane at left. You can then choose the field menu (the three dots) to the right of the field name and choose an action to take. The action is performed on all selected fields. You can select or deselect all fields at once by choosing either **All** or **None** at the top of the **Fields** pane. If you edit a dataset and exclude a field that is used in a visual, that visual breaks. You can fix it the next time you open that analysis. ## Searching for fields If you have a long field list in the **Fields** pane, you can search to locate a specific field by entering a search term for **Search fields**. Any field whose name contains the search term is shown. Search is case-insensitive and wildcards are not supported. Choose the cancel icon (**X**) to the right of the search box to return to viewing all fields. # Organizing fields into folders in Amazon QuickSight Organizing fields into folders When prepping your data in Quick Sight, you can use folders to organize your fields for multiple authors across your enterprise. Arranging fields into folders and subfolders can make it easier for authors to find and understand fields in your dataset. You can create folders while preparing your dataset, or when editing a dataset. For more information about creating a new dataset and preparing it, see [Creating datasets](creating-data-sets.md). For more information about opening an existing dataset for data preparation, see [Editing datasets](edit-a-data-set.md). While performing an analysis, authors can expand and collapse folders, search for specific fields within folders, and see your descriptions of folders on the folder menu. Folders appear at the top of the **Fields** pane in alphabetical order. ## Creating a folder Use the following procedure to create a new folder in the **Fields** pane. **To create a new folder** 1. On the data preparation page, in the **Fields** pane, select the three-dot icon, and choose **Add to folder**. To select more than one field at a time, press the Ctrl key while you select (Command key on Mac). 1. On the **Add to folder** page that appears, choose **Create a new folder** and enter a name for the new folder. 1. Choose **Apply**. The folder appears at the top of the **Fields** pane with the fields that you chose inside it. Fields inside folders are arranged in alphabetical order. ## Creating a subfolder To further organize your data fields in the **Fields** pane, you can create subfolders within parent folders. **To create a subfolder** 1. On the data preparation page, in the **Fields** pane, select the field menu for a field already in a folder and choose **Move to folder**. 1. On the **Move to folder** page that appears, choose **Create a new folder** and enter a name for the new folder. 1. Choose **Apply**. The subfolder appears within the parent folder at the top of the list of fields. Subfolders are arranged in alphabetical order. ## Adding fields to an existing Folder Use the following procedure to add fields to an existing folder in the **Fields** pane. **To add one or more fields to a folder** 1. On the data preparation page, in the **Fields** pane, select the fields that you want to add to a folder. To select more than one field at a time, press the Ctrl key while you select (Command key on Mac). 1. On the field menu, choose **Add to folder**. 1. On the **Add to folder** page that appears, choose a folder for **Existing folder**. 1. Choose **Apply**. The field or fields are added to the folder. ## Moving fields between folders Use the following procedure to move fields between folders in the **Fields** pane. **To move fields between folders** 1. On the data preparation page, in the **Fields** pane, select the fields that you want to move to another folder. To select more than one field at a time, press the Ctrl key while you select (Command key on Mac). 1. On the field menu, choose **Move to folder**. 1. On the **Move to folder** page that appears, choose a folder for **Existing folder**. 1. Choose **Apply**. ## Removing fields from a folder Use the following procedure to remove fields from a folder in the **Fields** pane. Removing a field from a folder doesn't delete the field. **To remove fields from a folder** 1. On the data preparation page, in the **Fields** pane, select the fields that you want to remove. 1. On the field menu, choose **Remove from folder**. The fields that you selected are removed from the folder and placed back in the list of fields in alphabetical order. ## Editing a folder name and adding a folder description You can edit the name or add a description of a folder to provide context about the data fields inside it. The folder name appears in the **Fields** pane. While performing an analysis, authors can read your folder's description when they select the folder menu in the **Fields** pane. **To edit a folder name or edit or add a description for a folder** 1. On the data preparation page, in the **Fields** pane, select the folder menu for the folder that you want to edit and choose **Edit name & description**. 1. On the **Edit folder** page that appears, do the following: + For **Name**, enter a name for the folder. + For **Description**, enter a description of the folder. 1. Choose **Apply**. ## Moving folders You can move folders and subfolders to new or existing folders in the **Fields** pane. **To move a folder** 1. On the data preparation page, in the **Fields** pane, choose **Move folder** on the folder menu. 1. On the **Move folder** page that appears, do one of the following: + Choose **Create a new folder** and enter a name for the folder. + For **Existing folder, **choose a folder. 1. Choose **Apply**. The folder appears within the folder that you chose in the **Fields** pane. ## Removing folders from the fields pane Use the following procedure to remove a folder from the **Fields** pane. **To remove a folder** 1. On the data preparation page, in the **Fields** pane, choose **Remove folder** on the folder menu. 1. On the **Remove folder?** page that appears, choose **Remove**. The folder is removed from the **Fields** pane. Any fields that were in the folder are placed back in the list of fields in alphabetical order. Removing folders doesn't exclude fields from view or delete fields from the dataset. # Mapping and joining fields When you are using different datasets together in Quick Sight, you can simplify the process of mapping fields or joining tables during the data preparation stage. You should already be verifying that your fields have the correct data type and an appropriate field name. However, if you already know which datasets are going to be used together, you can take a couple of extra steps to make your work easier later on. ## Mapping fields Quick Sight can automatically map fields between datasets in the same analysis. The following tips can help make it easier for Quick Sight to automatically map fields between datasets, for example if you are creating a filter action across datasets: + Matching field names – Field names must match exactly, with no differences in case, spacing, or punctuation. You can rename fields that describe the same data, so an automatic mapping is accurate. + Matching data types – Fields must have the same data type for automatic mapping. You can change the data types while you are preparing the data. This step also gives you the opportunity to discover whether you need to filter out any data that isn't the correct data type. + Using calculated fields – You can use calculated fields to create a matching field, and give it the correct name and data type for automatic mapping. **Note** After an automatic mapping exists, you can rename a field without breaking the field mapping. However, if you change the data type, the mapping is broken. For more information on field mapping for filter actions across datasets, see [Creating and editing custom actions in Amazon Quick Sight](custom-actions.md). ## Joining fields You can create joins between data from different data sources, including files or databases. The following tips can help make it easier for you to join data from different files or data sources: + Similar field names – It is simpler to join fields when you can see what should match; for example, **Order ID** and **order-id** seem as if they should be the same. But if one is a work order, and the other is a purchase order, then the fields are probably different data. If possible, make sure that the files and tables that you want to join have field names making it clear what data they contain. + Matching data types – Fields must have the same data type before you can join on them. Make sure that the files and tables that you want to join having matching data types in join fields. You can't use a calculated field for a join. Also, you can't join two existing datasets. You create the joined dataset by directly accessing the source data. For more information on joining data across data sources, see [Joining data](joining-data.md).