Introduction
In this tutorial, we will walk through setting up an Intersect sub query. A good way to think about Intersects is - use them if you need a list of something that appears in two seperate lists.
In this scenario our main report will have a list of athletes that attended camps in the current year. We will then create a list of athletes from the previous year, and only return a list of athletes that are in both. This will effectively give us a list of athletes that are return customers from last year.
Initialise
| Section |
|---|
| Column |
|---|
| 1. Click on the Create link and select Report to begin building your report. |
| Column |
|---|
|  Image Removed
|
|
| Section |
|---|
| Column |
|---|
| 2. You should now be on the Initialise Report page. Select the Drag and Drop Builder as the build tool. |
| Column |
|---|
|  Image Removed
|
|
| Section |
|---|
| Column |
|---|
| 3. Select Ski Team as the View. |
| Column |
|---|
|  Image Removed
|
|
Master Query
| Section |
|---|
| Column |
|---|
|  Image Removed
|
|
Intersect Query
Create Intersect
| Section |
|---|
| Column |
|---|
| 6. Click on the + button in the Sub Query panel to the left of the Data step to add a Sub Query. 7. Set the Type to Intersect and Style to Basic. 8. Click Ok to set up the query. |
| Column |
|---|
|  Image Removed
|
|
Configure Intersect
| Section |
|---|
| Column |
|---|
| 9. Set the Filter on the Master Query to apply to the Athlete ID field. This is how rows to be used from the master query will be located, matching the ID field. 10. Set the To Match field on the Sub Query to be the Athlete ID field. This will list all Athete ID values to be matched to the master query. 11. Drag the Year field into the Sub Query Filters list. This will be used to restrict the list of Athlete IDs to a certain date range. 12. Change the name of the Sub Query to Last Year. 13. Click Save to complete the Intersect. |
| Column |
|---|
|  Image Removed
|
|
Edit Settings
| Section |
|---|
| Column |
|---|
| 14. If you need to make changes to your sub query setup at all, click the Edit Settings link. |
| Column |
|---|
|  Image Removed
|
|
Apply Filters
| Section |
|---|
| Column |
|---|
| 15. Click on the Settings link in the Filters panel. This will allow you to configure logic and values for both filters. |
| Column |
|---|
|  Image Removed
|
|
Master Filter
| Section |
|---|
| Column |
|---|
| 16. Set the Master Query filter to be Year Between 17. Click the Define Value link and select the Pre-defined Period option. 18. Select the This Calendar Year option and click the Submit button. |
| Column |
|---|
|  Image Removed
|
|
Sub Query Filter
| Section |
|---|
| Column |
|---|
| 19. Navigate to the sub query filter by clicking on the Prior Years tab. 20. Set the filter to be filter to be Year Between 21. Click the Define Value link and select the Pre-defined Period option. 22. Select the This Calendar Year option and click the Submit button. 23. Close the Configure Filters panel. |
| Column |
|---|
|  Image Removed
|
|
Save
| Section |
|---|
| Column |
|---|
| 24. Click Report > Save to activate your report. |
| Column |
|---|
|  Image Removed
|
|
| Section |
|---|
| Column |
|---|
| 25. Set the Name of your report to Intersect Sub Query Tutorial. 26. Set the Description to This report was written using the Sub Query Tutorial. 27. Select Tutorial as the Category. 28. Select Training as the Sub Category. 29. Click Activate to finish. |
| Column |
|---|
|  Image Removed
|
|
...
| Column |
|---|
|
30. You now have an active Intersect Sub Query report. |
...
...
はじめに
