I am making a dashboard with health data from different sensors, such as heart rate and accelerometer.
Each sensors data is made into its own dataset and imported into Quicksight.
I have different labels in the different datasets. For example:
In the heart rate dataset, I have a calculated field, which is whether or not the heart rate is under 40 at any point within the last minute. A simple ifelse statement, which returns a string label
In the accelerometer dataset I have something similar. I check whether or not the accelerations exceeds a threshold over 50% of the time, within the last minute. This is another ifelse statement, which return a string label.
My question is: Can I make a table, which includes both of those labels, grouped by id?
Here is an example of two rows:
person_1, “over threshold”, “moving”,
person_2, “within thresholds”, “not moving”,
Whenever I search for answers, I mostly find SQL joins, but the data seems messy when i try that. Like my timestamps, which are dates in the visuals, seems to be turned into strings when i try to join.
When I try to join by user id, there I get errors from columns not matching.
Based on your description your common column from the two datasets is the Id . However, it is hypothetically possible that one Id has data in the first datasource and not the second one.
So the common dataset could possibly work correctly with the following approach
a. Get Patient details (Id, Name other details) into a separate table
b. Join table in a. to your two datasets as LEFT OUTER JOIN on the Id column
You should be able to get your details as your desire with this.
I am facing a similar challenge. I need to work with data from two large datasets that cannot be joined efficiently due to their size. Joining them creates significant issues, making it impractical. Unfortunately, QuickSight does not offer a solution for this scenario, which is a major limitation. In Power BI and other tools, handling such cases is straightforward. However, in QuickSight, the only option is to join the datasets, resulting in a large dataset with numerous blank and unrelated fields.
For example, when working with financial data, you might have a huge historical dataset for financial summaries, another for Accounts Payable (AP), and another for Accounts Receivable (AR). If I need to create a calculated field or visual that uses values from all these datasets, I am forced to join them. While the datasets share a common key column that could be used for the join, they also contain many dataset-specific fields. As a result, the joined dataset becomes unnecessarily large and cluttered with blank values.
A simple yet effective solution QuickSight could implement is to allow referencing data from different datasets based on mapped common key columns. This feature would save considerable time, effort, and storage space while enabling seamless analytics across datasets.
In the sample I have two datasets, hr and acc. Each has a table, showing a label, “attachment” and “movement”. Two basic labels, which are just examples.
I want one table, which shows both labels. How can I achieve this?
I tried to play around with making a joined dataset, using inner join and left (acc) join on time and id, but I ended up with no rows being imported.
It might be a challenge, as the sensors have different sample rates and therefor the timestamps aren’t identical.
I hope you can help. Again, I appreciate you taking the time
Hi @mikkel1,
Currently it is not possible in QS to create a visual using multiple datasets. As mentioned above, you would need to join into one dataset.
In regards to joining, I would not suggest joining on a date/time field, it would be more beneficial to join on some type of id field. That way, once you’ve joined, data that actually pertains to the same item (like an id) would be paired as opposed to separate data that just happens to share the same time/date.