Compare Selected Field Against Another Selected Field

Extractor allows you to compare Selected Fields against another Selected Fields chosen from a list of Special Value fields on the Add Special Value window. This enables you to extract and compare fields of the same type (i.e. date, text, and integer) based on set criteria.

Note:

  • When a selected field is being associated/ has been set for comparison with another selected field (i.e. as indicated in the Value of the Criteria tab), it cannot be removed from the list of Selected Fields. Doing so will display a warning message. If you intend to remove such field from the Selected Fields, you would first have to remove the criteria that is set for comparison then deselect the intended field.
  • The From Selected Fields option of the Select special value drop-down will only be available when there is at least another field in the Selected Fields list panel of same type as the current selected field.
  • Changing the Runtime Prompting Type from Guided (which is the recommended type) to Freeform would trigger a message warning you that such action will remove any criteria for the Special Value.
  • Changing the Function or Summary of a Selected Field with a Special Value added would trigger a message warning you that the current criteria may be invalidated and the Special Value may be removed.

 

To open the Add Special Value window in order to compare Selected Field against another Selected Field:

  1. Navigate to Extractor.
  2. On the Extractor's Design tab, add fields from the Available Fields to the Selected Fields list panel.

  3. On the Criteria tab, add an Operator for one of your selected fields.

  4. Adding an Operator in your Criteria enables the Special Value Prompt button. Click this button to open the Add Special Value window.

    On the Add Special Value window, choose the option From Selected Fields from the drop-down Select special value.

  5. This will enable the second drop-down field Selected Fields allowing you to choose from the Design tab's Selected Fields list panel the field you want to compare.

  6.  The selected field (e.g. Salary Package Effective End Date) from the Selected Fields list panel will be compared with another selected field (e.g. employee's Termination Date) in your extract.

  7. Save your extract and click Run on the Toolbar.

 

Extracting all employees on a Salary Package or who were on a Salary Package when they were terminated

As this is a case of extracting non-mandatory records (i.e. employee records with gaps where a current record is not required), instead of using the Effective Dating - Auto Add Criteria option, you can override the selection based on the Effective Start and End Dates (Remuneration -> Package -> Effective Start and End date).

  1. Add criteria to the Salary Package's Effective Start Date to check if it is in Today's date or in the Past, and make sure that the Start Date has already passed.

  2. Add criteria to the Effective End Date to check if it is Today's date or in the Future.

  3. Add the second criteria on the Effective End Date by clicking the New button, and select Is Exactly in the Operator drop-down field.

  4. Click the Special Value Prompt button to go to the Add Special Value window.

  5. On the Add Special Value window, select the Termination Date from the list of Selected Fields. The second criteria to the Salary Package Effective End Date will check and compare if the Effective End Date Is Exactly on the Termination Date.

With these three criteria in place, you will get all records where the Start Date is in the Past and the End Date is either on the Termination Date or in the Future.