Intersect multiple lists

Need help Database
1

Hi! I’m looking to find the union/intersection between multiple lists (i.e., more than 2).

Here is an example: I have 3 topics, each topic contains 2 subtopics. I’m looking to retrieve the common subtopics. How can I do this?

Topic1
- SubtopicA
- SubtopicB

Topic2
- SubtopicA
- SubtopicC

Topic3
- SubtopicA
- SubtopicD

The response should be SubtopicA. The numbers of topics and subtopics are dynamic and can be >10.

Thanks!

2

You can use the ‘intersect’ operator - this will find consistent values across multiple lists. Note that this will find the common value across all lists (i.e. if Subtopic A is missing from 1 list, it won’t show as a common value).

How to : Topic1 intesect with Topic2 intersect with Topic3 … etc.

Hope that helps

1 Like
3

The # of Topics can be dynamic, so its not as easy as manually chaining :intersects with .

An interesting question indeed :thinking:

4

Chaining :intersect should work if the subtopic has to appear in all lists.

5

Right but the list of Topics is dynamic, so you can’t point to specific lists

You need some trickery like “get all subtopics, but only show the ones where the # of duplicates is the # of topics”

6

Ah okay i missed the Topics part. If that’s the case what I’d do is do a count of how many times a subtopic appears and if the count is equal to the number of topics then it’s a match.

The easiest way is to create a text list of topicUID-subtopicUID (this ensures that the list is made up of unique items) and then just use a regex count matching the subtopicUID.

Side note: Hmm, maybe a regex count of Topic list :each subtopic UID might work…

3 Likes
7

Yep something like that sounds like it would work

8

Thanks @ihsanzainal84 and @tylerboodman !

I’ve set up a workflow in line with the above and it does what I need. It happens in two steps:

  1. Create a list formatted as text with the UUID of each subtopic. By doing this all duplicates are captured and can be counted
  2. Create another list that only adds a subtopic from a given topic when the condition in the advanced filter is met. The condition I use is along the lines of extract with Regex (the subtopic UUID) :count is list_of_topics:count. The Regex extract is made on the text list created in step (1). This verifies that the subtopic is common to all topics.
2 Likes