How can I select multiple lines from a table? (and how could I then DESELECT those items to exclude them form the analysis?)

Question & Answer
21

Hi

I know this subject is quite old, but I think that sharing my use case might help some people in the future.

I have 2 tables : Person and Car with a 1 to many relationship. These 2 tables are in 2 different PostgreSQL databases.

The idea is to be able to select several people and then show the cars that they own.
2 datasets have to be created separatly, because the Car table contains literaly millions of rows, meaning that creating a Spice is not an option.

The exact method described by darcoli can be used to select several rows in Person and create different calculated fields to filter the Cars.
Problem being that using calculated fields to filter a dataset containing millions of rows isn’t an option either.

The trick is to use custom SQL and parameter mapping to filter the table :

  1. Use custom SQL to get data from the “Car” table :
SELECT id, name, ownerId
from Car

  1. Create a single string parameter in the dataset named filter_ids with the default value being a comma

  2. Edit the SQL query to include the parameter

SELECT id, name, ownerId
from Car
where ownerId = any(string_to_array(<<$filter_ids>>, ','))
  1. Go back to the analysis in the parameter section and map the parameter “filter_ids” to “selected_item_ids”

Then you should be able to select any Person in the table and have the Car dataset updated accordingly

1 Like
22

How I wish Quicksight provides a feature which can perform like a wildcard entry and map those rows and get the entire data in one go. Despite so many of us requesting, This hasn’t been done :frowning: . I would really like to use the solution provided by @darcoli but I have other columns that severly gets impacted which can lead to accuracy issues.

1 Like
23

Hi :slight_smile: I followed @darcoli instructions, and it worked. I now have two tables: one contains all the data, and I can filter the other table by clicking on the rows of the first table.

However, my first table is very large, and concatenating the values across all cells is very time-consuming. Is there another way to use or implement “updated_selected_item_ids” more efficiently?
Tnx! )