Create a Query with a Union

Here's how you can create a query that includes a union of two subject areas.

Create a new analysis.

Create a Query with a Union

1. Click Create and select Analysis.

2. Open the browser search and search for Worker Assignment. Choose the Workforce Management - Worker Assignment Real Time subject area.

3. Click Workforce Management - Worker Assignment Real Time.

4. Collapse Catalog.

5. Expand the Worker Subject area. Now select the columns.

6. Select Person Number, Name, and Assignment Status Type Code.

7. Click Settings on the Assignment Status Type Code column, and select Filter.

   Now create a filter so only active employees are included in the report.

   1. Click the Operator dropdown and select is not equal to/is not in

   2. Set the Value to Inactive.

   3. Click OK.

8. Select Settings and then, click Delete.

   Now that the filter is created, you don’t have to see this column anymore. The filter still works even though you are no longer viewing the column.

9. Click Results tab. This shows a report of all active workers.

10. Click Table Properties and choose Content Paging to enable scrolling in the report.

11. Click OK.

12. Click the Criteria tab.

13. In the Selected Columns table, click Combine.

    Return to the Criteria tab and combine this table with another subject area.

14. Select and add the Compensation - Salary Details Real Time subject area:

    1. Highlight the first query and the Worker area to show the selected columns.

    2. Highlight the second query and the columns below. For the second query – Salary Details Real Time – the columns have dashed lines around them. This indicates that we need to pull in these fields.

    3. In Subject Areas, click Person Number and Name fields to add them to the second query.

15. Click Filter icon and add two more filters.

16. Add the first filter criteria to select active employees:

    1. Click More Columns and expand Worker.

    2. Select Assignment Status Type Code and click OK.

    3. Set the Operator to is not equal to/is not in

    4. Set the Value to Inactive.

    5. Click OK.

17. Click Filter and add the second filter criteria:

    1. Expand Salary and select Number of Days Since Last Salary ChangeClick OK.

    2. Select Days Since Last Salary Change.

18. In the New Filter dialog:

    1. Set Operator to is less than or equal to.

    2. Set Value to 366 (one year)

    3. Click OK .

19. Click Union in the Result column box.

    You can choose Union, Union All, Intersect, or Minus. If you choose Union, this displays all current workers including workers who have and haven't had their salaries changed in the last year. (This report isn't that useful. )

    Leave this at Union and return to the Results tab.

    Display the options but don't change them.

20. Click Results and then, click the Criteria tab.

21. Click Union icon. If you change the union to minus, you can view all current workers, minus those who have had a salary change in the last year.

22. Select Minus

23. Click Results tab. The report now shows all current workers who have not had a pay raise in the last year.