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.
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.
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.
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.
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.