How to Combine Multiple Data Sources/Datasets into One for Analysis?

[maxim]

Hi,

I have a QuickSight analysis with several data sources and datasets containing the same data structure but representing different time periods. For example:

• Data-Source-A with Dataset-A (June data, 20 records)
• Data-Source-B with Dataset-B (August data, 50 records)
• Data-Source-C with Dataset-C (November data, 30 records)

I want to combine all these datasets into one, so I can create visuals based on the overall data (100 records). Currently, when I use the “Suggested” feature in the toolbar to see suggested visuals, the results are based on the currently selected dataset, not all of them combined.

My questions are:

1. Is there a way to combine multiple datasets or data sources into a single dataset or data source in QuickSight?
2. How can I connect this combined dataset to my analysis and create visuals that use all records from June, August, and November?

I’d appreciate any suggestions or best practices for merging datasets from different periods into one dataset for a more comprehensive analysis.

Thanks!

[Giridhar.Prabhu]

Hi @maxim

Ideally, I would do a UNION of the data from the source through a Custom SQL.

But, if you want to do it in QuickSight the following discussions could help. Basically, the suggestion is a FULL outerjoin and then creating calculated fields to combine the multiple columns created from the three source datasets.

UNION two datasets together instead of JOIN?

Is it possible? I have two SPICE datasets created with Custom SQL with same columns, I want to use a UNION ALL to concatenate these datasets into one, but the only option that seems to be available in the visual editor is JOIN… Why isn’t UNION an option here? Is it hiding somewhere else? These are both small datasets which each only take about 2 minutes to load from their queries, but using UNION ALL within a single custom query for some reason makes it take so long that it is automatically can…

Analysis level joining or unioning of datasets for Authors

It’d be nice if there was a way to perform a union (append all rows from one dataset to another, more rows) or join (key based join, more columns, and more rows depending on join) at the analysis / dashboard level. Right now you can only perform these operations at the dataset level, for my use case users do not get access to dataset level operations just analysis / dashboard ones. Currently they are able to create calculated fields, which is awesome, but it’d be even better if they could mix t…