Joining datasets using geo fields

Learning Center Articles
,
1

As you may know, currently QuickSight allows you to join data by specifying join clauses in the dataset visual editor.

However in order to join data the columns used in the join clause need to comply with some restrictions, these are:

  • Calculated fields can’t be used in any on-clauses.
  • Geographical fields can’t be used in any on-clauses.

These restrictions don’t imply that you cannot have geographical fields in joined datasets, however, if you want to join by these field types you will be able to create an intermediate dataset. This is possible thanks to the dataset as source functionality.

Just follow the process outlined below to join datasets by casting geo fields as strings:

Create auxiliary dataset with columns containing geo data casted as strings:

  1. Create a new dataset choosing the two tables you want to join (don’t cast the geo fields, just leave them as strings).
  2. Join the data using the columns containing geo data as string, then save the dataset.
    Capture
    and then exit the dataset preparation page by clicking on cancel capture_1

Create new dataset referencing the previously created one

  1. Browse to the dataset you created above and use the :arrow_down_small: next to ‘USE IN ANALYSIS’ to select ‘USE IN DATASET’.

    image
    image1371×494 43.7 KB

  2. Change the data types of the fields that need to be geo fields to the appropriate geospatial type.

  3. Click on Publish & Visualize capture_2

Now you will have a dataset with the data joined and available in geographical format ready to visualize.

Happy dashboarding!!

2 Likes