NOMAD/UltraQuest ALPHA Operator Explained
UltraQuest and NOMAD have several powerful built in operators to help with screening data for reporting.
The ALPHA function checks a character expression and returns a TRUE condition if the expression contains only letters or blanks, and a FALSE condition if it contains any digits or any other characters.
Problem: You have a text field, STATE_CODE, that should only contain letters and because of data entry errors some of the records have numbers in the field. To determine which records needs to be corrected you want to create a list of records that STATE-CODE has numbers in the field.
To add a column to UltraQuest Reporter, go to the ‘Columns’ dialog, press [Add (x*y)+2] to open the Expression Column dialog, select ALPHA from the ‘Operators & Functions’ drop down choosing ‘Alpha Functions’ then scroll down and select ‘ALPHA(character-expresion)’ and press [Insert].
In the ‘Expression’ box change to the template inserted to:
IF ALPHA(STATE_CODE) THEN ‘YES’ ELSE ‘NO’
‘Column Format’ to A3
‘Heading’ to ‘BAD:RECORD’
You now have your column in your request.
Then go to the ‘Data Filtering’ dialog and select ‘BAD_RECORD’ and press [ Add > ] to move the column over to ‘Columns in filter’ and press [ Build Filter ]. Next set ‘Operator’ to ‘EQ’, enter ‘NO’ into the text box and press [ Continue ]
DEFINE BAD_RECORD AS 3 = IF ALPHA(STATE_CODE) THEN ‘YES’ ELSE ‘NO’;
SELECT ADD BAD_RECORD EQ ‘NO’;
Now when you run the request it will only contain the following accounts:
Please feel to contact UltraQuest and NOMAD support if you have any additional questions about the ALPHA function.
More information about the ALPHA function may be found in the "Functions and Operators" section of Chapter 5 of the UltraQuest and NOMAD Reference Manual.
Next week’s topic: Operators Explained