How to Create Subqueries in Data Analyzer with Personify Member Data
Learn how to create subqueries in Data Analyzer using your Personify360 data, for deeper insight into your member data (e.g. identify members who attended two consecutive meetings).
IntelliData is a proud Solutions Partner
Join IntelliData President, Denny Lengkong, for an informative video training series, that will guide you through Personify360's most important data management and business intelligence features.
Don't forget to subscribe to get notified of new additions to the Personify video training series, and for more tips and tricks to help you make the most of your data!
CHECK OUT MORE PERSONIFY360 TRAINING VIDEOS
"How to Create Subqueries in Data Analyzer" Video Transcript
Subquery is basically a query inside a query. In Data Analyzer, there are two ways to do subquery.
In this example that I'm going to show you, we're going to find out which registrant went to two consecutive meetings.
Here, I have two queries.
One is to look for registrant who went to 2015 annual meeting, and the other query is for...pretty identical query except it's looking for registrant who went to 2016 annual meeting.
When I run this report, when I run this query, I have two result set. One lists all the registrant who went to 2015 annual meeting, and we have 42 people.
And the other report is for people who went to 2016 annual meeting. In this case, you only have two registrants. The first way we do the subquery is you go back to Data Access, click on Edit.
♪ [music] ♪
Now, remember, for the first meeting, we're looking for 2015, and the second meeting, we're looking for 2016.
Now, if you wanted to look for people who went to 2015 and 2016 meeting, I could easily drag this Registrant Master Customer ID as part of my filter.
Okay. And on my Option, I could click on Result from another query. And when I click on that, it's asking me which field that I'm going to use from meeting two, which is my second query.
So in this case, I'm going to pick the Registrant Master Customer ID.
So what this query says, give me all the IDs of the people who went to 2015 annual meeting whose ID also exists in the other query, which is the query for people that went to 2016 annual meeting. When I run this query...
♪ [music] ♪
I have one person. Because if you look at this, Devki went to 2016 annual meeting and he also went to 2015 annual meeting.
So if I go back to… So Devki is the only one who went to both. So if I went back to the query editor and removed my filter and I rerun it... ♪ [music] ♪ Devki's ID is 2012, I believe.
And if you look at here, Devki is there. So he went to 2015 annual meeting and he also went to 2016 annual meeting, right there. So that's one way of creating subquery.
Another way of creating subquery, it's almost similar to what we did before except,I went back to my…going back to my initial query.
Click on the Edit, and instead of dragging the Registrant Master Customer ID, I'm going to click on this, and I'm going to click on the Subquery button, and that creates a subquery object.
Now in here, I need to drag a meeting or session code. And I need to specify…if I don't know what the meeting code is, I can click on the Values from list and search for the value.
In this case, I'm going to look for 2016 annual meeting. So again, what this query says, give me all the IDs of all the people who went to the 2015 annual meeting.
And pass the ID and match the ID with the people who went to 2016 annual meeting. So if we rerun this query...again, I'm going to get one person because Devki's the only one went to two annual meetings.
Now I could reverse it by making it Not in list. What that means is everybody who went to 2015 annual meeting who did not go to 2016 annual meeting.
So in this case, we're going to get the other 41 registrants for 2015 but didn't register for 2016. So this is a pretty good example for meeting department if you want to look for two different meetings that...looking at registrant who went to two consecutive meetings.
So as you can see, once it returns, it returns with 41 registrant. Now, the catch with this is with this subquery, you can only do it within the same universe.
However, when you create with the other way of doing…when you get a way to create subquery, you could create it with different universe. And I'll get back to that point.
But back to this point, if I want to make it a little more dynamic, I could change this to make it a prompt. And I also could change this to make a prompt.
And I'm going to change this to...I'm going to call it Meeting Code 2. So when I run this report, it's going to ask me which meeting you want to see it first and which meeting you want to see second.
So if I want to pick 20…Let's say I want to see...give me anybody who went to 2011 meeting and also people who went to 2012 annual meeting.
♪ [music] ♪
See, there are four people. Actually, these are… My query was actually looking at people who went to…for the first meeting, but did not go to the second meeting.
So I need to change this back to an In list. Again, I want to rerun. So basically, give me everybody who went to the first meeting and also went to the second meeting.
So again, the query was asking, give me the list of people who went to 2011 annual meeting and also went to 2012 annual meeting.
So when I ran it… There are actually none that went to both meetings. All right. But then again, I could rerun this query and change it to any meetings.
I can pick the first meeting as 2010GALA, for example. ♪ [music] ♪ And the second meeting, I'm going to pick… ♪ [music] ♪ 2012 annual meeting, for example.
♪ [music] ♪
And again, there's no data. Reminds me that this dataset is very small. So there might not be a lot of registrants that went to consecutive meetings.
But one thing that I need to point out, that when you do a subquery like that with this method, if you look at the sequel, it's actually doing a true subquery in the sequel statement.
But when I...if I remove this and if I do the original, the first method that we did by looking at… Oops, sorry. By looking at results from another query and picking that.
Actually ,I'm going to change this to 2014 [inaudible 2015. 2015 annual meeting and also pass the people who went to 2016 annual meeting. When I run it, again, I only see one person.
But I think my result is going to be just Devki right there. But if you see the sequel... Go back to my Edit.
If you see the sequel, it is actually passing the ID of all the people that went to 2016 annual meeting. In this case, there are only two IDs, so it's not a big deal.
But if your query has a lot of IDs, this might limit your result set because there's a character limitation that's going to prevent that.
So my preference, if you do a subquery, is by doing it the second option, which is by clicking the subquery and adding your word statement here.
That's how we do subqueries in Data Analyzer. Feel free to reach out to us if you have any questions.
Thanks for watching. Hope that was helpful. I'm Denny Lengkong with IntelliData. See you next time. ♪ [music] ♪