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 list and select is not equal to/is not in

   2. Set the Value to Inactive.

   3. Click OK.

8. Select Settings. 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 Delete and then, click the Results tab. This shows a report of all active workers.

10. Click the Table Properties icon Turn on Content Paging to enable scrolling in the report.

11. Choose Content paging. Click OK.

12. Click the Criteria tab.

13. In the Selected Columns table click the Combine icon. Return to the Criteria tab and combine this table with another subject area.

14. Open the browser search.

15. Search for and select Compensation - Salary Details Real Time subject area. Add the Compensation - Salary Details Real Time subject area.

16. Highlight the first query and the Worker area to show the columns that are selected. The first query is here.

17. Highlight the second query and the columns below.

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

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

19. Click the Filter icon and add a filter.

20. Click More Columns

21. Expand Worker and select Assignment Status Type Code.

    Click OK.

22. In the New Filter dialog:

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

    2. Set the Value to Inactive. In the New Filter dialog, choose is not equal to/is not in for the Operator, and Inactive for the Value.

23. Click OK.

24. Click the Filter icon. Now, add the filter criteria Number of Days Since Last Salary Change.

25. Expand Salary

    Select Days Since Last Salary Change.

26. Click OK.

27. In the New Filter dialog:

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

    2. Set the Value to 366 (one year)

    3. Click OK .Now set a date range of one year.

28. Click the Union icon in the Result column box. You can choose Union, Union All, Intersect, or Minus.

    If you choose straight Union, it will show all current workers, then all workers who have had their salary changed in the last year.

    Leave this at Union and return to the Results tab.

29. Click the Results tab.

30. Click the Criteria tab. This report isn't all that useful.

31. Click the Union icon But if you change the union to minus, you will get all current workers, minus those who have not had a salary change in the last year.

32. Select Minus

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

    1. Show the Sets and Venn Diagrams graphic. This diagram shows the difference union - this is the minus

       This is a normal Union.

    2. Highlight the A\B difference graphic in the diagram.

       This is the Intersection.

    3. Highlight the A\B Union graphic in the diagram.

    4. Highlight the A\B Intersection graphic in the diagram.

34. Click the Criteria Tab. Now try an intersect union.

35. In the Result criteria, click the Union Icon

36. Select Intersect.

37. Click the Results tab. You can see all workers who didn't receive a pay raise in the last year.