Joining and Filtering datasets

Hello,
I have a Dataset A, which is created based on a custom SQL query which includes the StartTimes and EndTimes and also Dataset B which includes a DateTime column with other data and I want to filter out the data in Dataset B based on the Start and end dates in Dataset A.
What is the best way to achieve this in Quicksight?

Example
DataSet A

id id1 StartTime EndTime
id1 id1_1 2024-11-01T00:00:00Z 2024-11-02T00:00:00Z
id2 id2_1 2024-12-01T00:00:00Z 2024-12-02T00:00:00Z

DataSet B

id_b idb_1 DateTime
idb1 idb1_1 2024-10-01T01:01:00Z
idb2 idb2_1 2024-11-01T01:01:00Z
idb3 idb3_1 2024-12-01T02:01:00Z

Result (Filtered DatasetB)

id_b idb_1 DateTime
idb1 idb1_1 2024-10-01T01:01:00Z

As you can see the Result dataset is filtered by removing the data based on the StartTime and EndTime information available in Dataset A.

Would really appreciate any help on this?

Hi @abhtriv

If both the datasets are from the same database I suggest creating a dataset using Custom SQL doing the join with date range. Within QuickSight dataset creation the range based conditions are not available in the join between two tables.

Regards,
Giri

Hi @abhtriv,
What is the logic you’re using from dataset A to filter Dataset B? I’m not quite sure I see how the start/end times are filtering down to that result you provided.

In regards to general practices with joining and filtering datasets; to setup a filter from one dataset to another, you’ll need each dataset to share a field that you’re using to help filter.

For this instance, you may need to explore joining your datasets, which can be performed as long as there is one matching field that you can use to join them.

Let us know if you have any additional questions!

Hi @abhtriv,
Following up here again as it’s been awhile since we last heard from you; did you have any additional questions regarding your initial post or were you able to find a work around?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

Hi @abhtriv,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!