The flag records feature offers a powerful way to track a specific subset of records from your data. You can use this feature to identify records that meet certain criteria and then use those flagged records in tabulations.
For example, consider a student records database. You can identify those students who had a 100% attendance record in February and label their group with a name of your choice. You could then look at their attendance record across every other month of the school year. You could do this by each student in the 100% group to get an individual view of their attendance student by student, then compare their exam results with the rest of the student body.
Flagging records identifies and tracks the actual underlying unit records, so this can be particularly useful when performing studies over time on groups of specific individuals, such as a specific set of customers or patients. If you flag a set of records, and then your SXV4 database is updated with new data, then your flagged group will continue to contain the same set of unit records, even if those records no longer match the original criteria.
When you flag a set of records, SuperCROSS will automatically create two recodes in the Fields window. One of these recodes will contain the individual records so you can list them all in a table. The second recode will be a group containing all of the flagged records. This can be used as a universe or subject population when defining other queries.
Step 1 - Decide how you want to Identify Records for Flagging
There are two ways you can select the records you want to flag:
The simplest way to select records for flagging is to create a table that identifies the records you want, and then select the relevant cells. For example, if you wanted to flag students with a 100% attendance record in February, you would add the month field and the attendance field to a table and then select the cell that intersects 100% attendance and the month of February. When you use this cell as the basis for flagging records, all the individual unit records that contribute to the cell will be flagged:
Alternatively, you can select the records you want to flag by creating a record identifier file. This is simply a plain text file (.txt) with a record identifier on each line. The record identifier can either be a value from the primary key field of your fact tables (such as a unique customer, student or patient ID), or a numeric value from one of the summation options in your database (such as Customer Profit or Average Account Balance).
Step 2 - Instruct SuperCROSS to Flag the Records
When you have created your record identifier file, or selected the cells you want to flag, do the following:
Select Cross > Records > Flag Records or right-click the table and select Records > Flag Records.
The Flag Records dialog displays.
- In the Name field, enter a name of your choice for the flagged records. This will appear in the Fields window once you have finished setting up the flagged records. It must be unique within this database and cannot be the same as the name of any existing field, recode or user defined field.
In the Record Identifier field, select the field you want to use as a key to identify the unit records. The appropriate choice will depend on what you are trying to achieve:
You are using a Record Identifier File
If you are using a record identifier file, then you must select the field that matches the record identifiers you used in your file.
For example, if your file contains a list of unique customer IDs, choose the customer ID field from the drop-down list.
You want to flag the specific records that contribute to the selected cells
If you want to select only the records that contribute to the selected cells, then you must select the unique primary key field for the relevant fact table.
For example, suppose your selected cell shows 91 students had 100% attendance in February. You would select the primary key of the student record fact table, and SuperCROSS would flag the 91 records contributing to this result.
If your database has multiple fact tables, then you may wish to choose the primary key of a different fact table to the one shown in the table. In this case, the number of flagged records may not match the number of records shown in the selected cells.
For example, if you have selected the cell that represents the number of customers in the Retail Banking database who are male and divorced, then you might choose to select the primary key of the accounts fact table, rather than the customers fact table. This will flag all the account records belonging to those customers, rather than flagging the customer records.
You want to flag records from the entire database that match the values of a summation option on the records contributing to the selected cells
You also have the option to select one of the other numeric fields (summation options) from the drop-down list.
In this case, SuperCROSS will:
Look at all the records that contribute to the selected cells.
Check the value of your selected numeric field in all of those records.
Find and flag all the records in the entire database where the value of that numeric field matches any of the values for that field in the records that contribute to the selected cells.
For example, suppose your cell selection identifies 42 customers who are male, divorced, and live in New South Wales, but you choose to flag records based on Customer Profit. In this case, SuperCROSS will check the Customer Profit of those 42 customer records, and then find and flag all records in the entire database that have the same Customer Profit value as any one of those 42 customers.
In most cases, when you choose this option the number of records that are ultimately flagged will be much bigger than the number of records that contributed to the selected cells.
- In the Create From field, select whether you want to use the Selected Cells or a Record Identifier File that you have prepared.
- If you are using a record identifier file, click Browse and select your file.
- Click OK. SuperCROSS adds your flagged records to the list of fields in the Fields window.