05 job 040d parameters screen

© 2019 - State of Utah - Department of Technology Services
Jump to navigation Jump to search

Job 040D Parameters Screen

Es2n - job 040d - micro selection report parms.png


This job’s parameters are used to generally mimic the output of Job 020D, but with input parameters that use more of a database query structure. Up to nine selection conditions are established with and/or/lor (for linked-or) combinations and options for a single comparison value, a range of values, or comparison against another field in the micro record, micro-auxiliary record, or wage summary record, either directly or with an offset value. Single-byte fields can even compare against a list of values. The screen layout is shown (above) with a sample set of conditions.


The sample screen shows how the parameters appear as they are entered, after the Enter key is pressed. Fields, values, etc., are reformatted to become more readable and the field ID’s name is displayed on the right.


There are a number of differences between this screen’s layout and that of the 020D parameter entry screen; in fact, the only similarities are on the top and the bottom. The top portion bears a slight resemblance to 020D in that it has a year/quarter field and selection switches for employment and wage data inclusion in the report. If the year/quarter field is left as a default “YYQ” value, then the output report/file will use current-quarter fields as a default, regardless of which quarters are involved in data selection. There is no report type option for 040D, since this job will always produce a firm list type of output (shown as report type “1” for 020D). Other similarities are in the bottom portion, where the same sort sequence options can be selected, and a report, file, a delimited file, or group output type can be selected.


The sort sequence parameter character codes can be drawn from the following options:

  • A - SESA ID ascending sequence
  • C - County FIPS code ascending sequence
  • E - Employment (third month) descending sequence
  • I - Industry (i.e., NAICS code) ascending sequence
  • M - Mailing address ZIP code ascending sequence
  • N - Trade name or legal name ascending sequence
  • O - Ownership code descending sequence
  • Z - Sub-county zone or township ascending sequence


Output type codes are selected as “R” for a printed report, “F” for a data file output (using the Micro Output Data File format), “D” for a delimited file output that can be loaded into Excel, or “G” Group File output of selected SESA ID’s. When a Group output is selected, the group name will match the request ID.


The biggest dissimilarities of this screen to the 020D parameters are in the nine selection parameter lines that identify which fields to use for selections, what values should be included or excluded, or against which other fields to compare them. Fields can be identified by their ID code or by the beginning position number in the Micro File record. For instance, the Reporting Unit Description field can be accessed either as “RUD” or 0164 (its first character location in the micro record); however, since numbers are generally harder to remember, the mnemonic is used more often. The usage of a starting character position was taken from the original 040D design, which attempted to emulate the FileAid system for selecting records based on values found at selected character positions.


Four lines of explanatory text precede the headings for the nine parameter lines. What follows is a restating of those four lines in a more comprehensive context. The selection fields use the following headings:


  • AND/OR/LOR = Selection switch to connect the statement above to the statement on this line with an “OR” (or simply “O”), an “AND” (or just “A”), or an “LOR” (or only the “L”) logical operator. Note that the first selection line has no “AND/OR” field, since it is the initial condition, so all connectors must follow it. “And” conditions are linked together, while “Or” conditions act as separators between sections of selection lines. “LOR” is short for a “linked-OR” condition, which is subordinate to the “And” conditions (essentially like an inner set of parentheses for either/or option branches). These connectors will all be described in greater detail later.


  • FIELD IDENT = Field identification code, an alpha-numeric code for the field (which is translated into a text description at the end of the line).


  • Q/T = Quarter identifier - used only for quarter-specific data elements that are referenced by field ID. The quarter can be listed as a number (‘1’ through ‘8’), identifying newest (future) to oldest quarters, respectively, or a letter code of “F” (future quarter (same as “1”)), “C” (current quarter (same as “2”)) or “P” (prior quarter (same as “3”)).


  • CD = Comparison code. This can show either the alpha code (e.g., ‘EQ’) or the symbolic (e.g., ‘=’) notations of the codes. If a range is used, only the equal and not-equal (i.e., ‘EQ’, ‘NE’, ‘=’, ‘<>’) values can be used, as this notes whether to include accounts that are inside (equal) or outside (not-equal) the specified range. If an “add-on” value is used with a field-to-field comparison, then the opposite is true (i.e., the comparison must be a larger-than (‘>’, ‘>=’, ‘GT’, or ‘GE’) or a smaller-than (‘<’, ‘<=’, ‘LT’, or ‘LE’) value to augment the comparison with an added value or percentage). Additionally, the “contains” (‘CT’ or ‘()’) option can look for text characters within a longer text field. The available values include:


  • ‘>’ or ‘GT’ - ‘Field Ident’ value exceeds the ‘Start of Range’ value or the value in the comparison Field ID (either of which will be in the column immediately to the right of the “CD” field). This is not valid for value-range use, or with a series of values; however, this can be used with an add-on value in a field-to-field comparison (as is the ‘>=’ / ‘GE’ option below).


  • ‘>=’ or ‘GE’ - ‘Field Ident’ matches or exceeds the ‘Start of Range’ or comparison Field ID; not valid for value-range use or with a series of one-byte values.


  • ‘=’ or ‘EQ’ - ‘Field Ident’ matches the ‘Start of Range’ or comparison Field-ID. If an ending-range value is specified, then ‘Field Ident’ must be between the starting and ending value (inclusive). If a list of single-byte values is shown, separated by commas, then any one of those values can be matched as a successful condition.


  • ‘< >’ or ‘NE’ - ‘Field Ident’ must have a different value than the ‘Start of Range’ or comparison Field-ID. If an ending-range value is specified, then ‘Field Ident’ must be either below the start-of-range value or above the ending-range value. If a series of one-byte values are specified, then the selected field must not match any of the values shown.


  • ‘<’ or ‘LT’ - ‘Field Ident’ value falls below the ‘Start of Range’ value or comparison Field ID; not valid for value range use, nor with a byte-value series; however, it can be used in a field-to-field comparison with an “add-on” in the Ending Range field (as can ‘<=’ / ‘LE’, described next).


  • ‘<=’ or ‘LE’ - ‘Field Ident’ does not surpass (i.e., matches or falls below) the ‘Start of Range’ value or the comparison Field ID; not valid for value range use, nor with a list of single-byte values.


  • ‘( )’ or ‘CT’ - ‘Field Ident’ contains the characters specified in the ‘Start of Range’ value. This comparison can only be used with text-type data fields in the ‘Field Ident’ (e.g., you cannot check to see if the taxable wage field contains “XYZ”).


  • Value, Field-Q, Start of Range - This area can either be used to specify a comparison value, a series of single-byte values (separated by commas), the beginning of a range of comparison values, or a comparison field ID (either administrative or quarterly). If a comparison value is used against an alphabetic field, its length will determine how many characters will be compared (which will always be the case in a “contains” comparison). If a field-ID is specified, it will be shifted to the right-hand side of the field area to keep it separate from the comparison values once the Enter key loads the data.


  • Ending Range (Optional) - This field has two possible meanings. When used in a range comparison, the comparison code (“CD”) must be set to equal or not-equal (i.e., “EQ”, “NE”, “=” or “<>”), and both the “Value, Field-Q, Start of Range” (just mentioned) and this field must show a value consistent with the type of data in the Field-ID. When used as an “add-on” for field-to-field comparisons, the field ID must represent a numeric data item, the comparison code must denote a greater/smaller comparison (i.e., “GE”, “GT”, “LE”, “LT”, “>=”, “>”, “<=”, “<”), the “Value, Field-Q, Start of Range” field must show a field-ID, and this “Ending Range” field must show either a numeric or percentage value, to be added to, or removed from, the comparison value. This sounds confusing initially, but the whole process, and the options available, will be described in more detail shortly.


  • Field Name / Description - This field is not entered. The program displays a description here, assigning a name to the “Field Ident” field. This can be helpful to confirm that the five-byte ID code is correctly entered and to ensure that the quarter code matches the desired quarter of data. Quarter-specific data items are also listed with the actual quarter ID (e.g., “17/3” for third quarter of 2017) in parentheses.


In the sample shown, it is checking for one of these cases:

End-of-liability date is in 2017/4 quarter and some comment code in current quarter is 93 – or the current quarter minimum employment exceeds the prior quarter minimum employment by at least 10%

OR

The latitude is not between 41 and 42 degrees and the prior-quarter tax rate exceeds 2.5% and the prior-quarter MSA is 01234

OR

The first line of the Physical Address contains a PO box somewhere.


As you can see, the “LOR” is combined with the “AND” statement, but the regular “OR” statements act as separators.


The field identification abbreviations are supplied in the list that appears on the next page. If it is a quarterly-occurrence item, the “Q” column will carry an “x” (meaning it can use values of 1-8, F, C, and P); otherwise this area will be left blank (as it will appear on the screen as well). Wage Summary File fields will use the same quarter subscripts as the Micro File record, even though they will not be found in that position in the Wage File. Also note that some fields are up to 35 bytes long. However, the comparison strings are limited to 15 characters. If this limit is insufficient (such as in finding a city name of “Northwest Abercrombie Heights” vs. “Northwest Abercrombie Ridge”), then the field offset approach can be applied (one of the rare instances where it is an acceptable practice) for the remaining portion of the comparison string (i.e., compare the first 15 bytes to “NORTHWEST ABERC”, and use the offset of 15 past this position for “ROMBIE H”). These types of actions should only be attempted with text type data (names and addresses). Please note that this type of over-15-character usage would be extremely rare.


This table uses some color-coding of the entries. Items that are found in other files (the Micro Auxiliary File or the Wage Summary File) use light green shading, while “interpreted” fields (derived from calculations or examining multiple fields at once) show up in pink shading.


The File Record Layouts documentation provides more elaborate micro field descriptions, valid codes, etc. It also includes beginning character positions in case you need to access something from the midst of a field. The “x” in the quarter (“Q”) column denotes a quarter-specific data item. The valid quarter code values include the following: “1” or “F” (future quarter), “2” or “C” (current quarter), “3” or “P” (prior quarter), “4” (second prior quarter), “5” (last year’s future quarter), “6” (last year’s current quarter), “7” (last year’s prior quarter), “8” (last year’s second prior quarter). The letter codes and numeric codes can be used interchangeably, as can be seen in the sample screen earlier.


Table 01.png


Table 02.png


Table 03.png


With so many field ID’s, it can be difficult to remember which ones you are after. But there is a helpful-hint feature available within the Field ID area itself. When you come to the point where you need a list of valid field ID’s, just enter a question mark (“?”) instead of a field ID code to get a complete list of the valid codes, along with their descriptions. If you only need a partial list (for instance, everything beginning with an “M”), you can type in the first part of the field ID you’re looking for, followed by the question mark (i.e., “M?”), so that the list will start at that point. Although you can obtain whichever field ID you want in this way, the listing mode can only be stopped by exiting back to the menu screen with the F1 key. Because of this, entering other lines to the parameters requires you to re-enter the parameter set by re-selecting it on the 040D menu screen.


Knowing the field ID’s is only half the battle, however. The trick is to combine these fields into a meaningful and useful data selection method. As noted previously, you can enter various comparison codes and data ranges or alternate comparison fields. Obtaining the desired result is essentially converting your verbal intent into the mnemonics of the fields and comparison structures. Knowing when to use a range, a value list, or when to use a combining “AND” or linked-or (“LOR”) vs. a separating “OR” are all part and parcel of setting up the Job 040D data selection equations.


The initial screen shown several pages earlier, for instance, expresses several selection criteria, set up as a hodgepodge to demonstrate the various field types and formats. Some are linked together with “AND” connectors, one with a linked-or (“LOR”), while two sets of conditions are separated by an “OR” condition. This sample is not specifically designed to be practical, but points out several of the options available for parameter selections. Let’s look again at the parameters translate them into more meaningful language.


Es2n - job 040d - and or lor.png


The parameters themselves are shown above without the rest of the screen. The “OR” statements on the fourth and seventh lines separate the parameters into three sets. If any one of these three sets is “true”, then the record will be selected. The first line is demonstrates that a range of values is possible for a date (in this case, the end-of-liability date (“EOL”) occurring during fourth quarter of 2017), an employment, a wage field, a tax rate, etc. This one uses the range check feature, so that the EOL must be in the range 10/1/2017 to 12/31/2017, inclusive (since the comparison code is equal (“=”)). If the comparison code had been not-equal (“<>” or “NE”), then it would select any accounts with an EOL date either before 10/1/2017 (including unset EOL dates), or after 12/31/2017. If an account matches the EOL date range, it is “tentatively selected”, meaning that it is in “so-far-so-good” status, but could go either way, depending on the remaining conditions.


The second line uses an “AND” connector, meaning that it is must also be true for the record selection to take place. It uses the “CMT-X” field for current (“C”) quarter, which means it will accept a match in any of the three comment codes for the quarter. If the ‘93’ value can be found in the current-quarter comment codes, the account will remain in “tentatively selected” mode; otherwise it will be reduced to “tentatively unselected” mode. There are essentially four selection-mode levels, which could be equated to a range from “bad” (not selected) to “good” (selected). The two in-between levels could be equated to “not bad” (tentatively selected) and “not good” (tentatively unselected). At the end of the parameter checks, we will end up on one end or the other, but we need to go through the decision-making process.


It should be noted that a “contains” clause (using “( )” or “CT” notation, described later in this section), could have been substituted. However, if the comments were “99” and “32”, then the six-digit comment field could be “9932” which would “contain” a “93”, even though none of the comments was actually a “93”. It is better to check the CMT-X field to see if any of the comment fields is set to “93” rather than the comment code contains a 93. 


Note: Once a parameter subset (those linked with “AND” and “LOR” conditions, or all by themselves as an individual “Or”) is true, the other conditions (i.e., other “Or” groups) are not checked, since the record can be selected based on that parameter subset.  Similarly, when one of the statements linked by “And’s” into a parameter subset is false, the remaining “And’ed” conditions are not checked, since, even if they were true, they could not select the establishment because of the earlier failure in a linked condition.


So why do we need the “tentatively unselected” mode? If the CMT-X condition was not met, what keeps the process alive? That answer is found in the third parameter line, with begins with a “linked OR” (“LOR”) condition. This statement checks for the minimum employment (“MIN-E”) in current quarter (“27”) exceeding the minimum employment (“MIN-E”) of the prior quarter (“24” in this case). The “by 010%” portion declares how much larger the current minimum employment must exceed the prior quarter minimum employment. The 10% limit means that a MinEP = 50 and MinEC = 54 situation would not be selected (since 54 is only 8% larger than 50), but in MinEC = 56, then it would be acceptable. This line acts as an alternative to the second line. If it is true, the status will be augmented from “tentatively unselected” to “tentatively selected” again. To state the first three lines together, we could write it as- “If the EOL date is somewhere in 17/4, and current quarter has either a ‘93’ comment code or minimum employment is over 10% higher this quarter than in prior quarter, then select the record.”


This also demonstrates the “MIN-E” which could just as easily been the “MAX-E” value, or maximum employment for the quarter. These values are the minimum or maximum of three different values, the first, second, and third months of employment. This helps in setting up fewer parameters. Rather than saying “MON-1(P) < 40 AND MON-2(P) < 40 AND MON-3(P) < 40”, you can simply state “MIN-E(P) < 40” to have it all done in one line. Also, by having the comparison of two of these fields, and introduction of the “BY” percentage, allows for comparison of one field against another and the percentage must meet or exceed that specified.


In line 4 we come to an “OR” connector, which means that the previous condition “block” has come to an end. If we are still at “tentatively selected” mode, then nothing further must be checked, so the record is selected. At this point, none of the other conditions need to be checked, since we already have a “good” record. For records that are in the “tentatively unselected” mode (passing the first check, but failing the second and third), or in the “unselected” mode (failing the first check), we cannot select the record yet. However, the “OR” brings up a new set of conditions to test; if these are true, the account can still be selected.


Line 4 is looking for establishments that have a physical location address outside two degrees of geographic latitude (“LAT”); this will be a true statement if the latitude is below 41° or at least as high as 43°. Actually, the range was entered as “41” and “42”; the system automatically adds zeroes to the starting value and nines to the ending value of the entered range, which is why they show up as “41.000000” and “42.999999”. Geographic longitude and latitude use millionths of a degree as their base of measurement. The size of one square millionth of a degree varies with latitude, since longitude lines get ever closer together the closer you get to the poles. On average, through the continental United States, one square millionth of a degree is a little bigger than the size of a playing card (3.6 by 4.4 inches). If the latitude is in the selected range (i.e. it is OUT of 41° to 42° range), the establishment is tentatively selected; if not, it is tentatively unselected.


Please note that Line 4 is a range that uses “outside the range” with a not-equal (“<>” or “NE”) specification. Certainly, anything that would be below the minimum, even an unset of zero-valued latitude, would be tentatively selected. It is therefore better to use “inside the range” specifications, so that there is not a need to specify that the field is greater than zero, as another line.


It is also possible to select geographic longitude values (“LONG”), but one caveat needs to be mentioned here. Longitudes are listed as negative values for the Western Hemisphere. The reason for this is so they will graph correctly. Since the farther west you go, the larger the longitude, the only way for it to appear correctly is to use the negative values so the higher longitude numbers will reflect a subsequently farther-left placement on the map.


Line 5 is looking for tax rates (“TAXRT”) in the prior quarter (“3”) that are larger than 2.5%. So any rate of 2.501% or higher will be accepted. However, in order for this condition to be checked, the latitude check from line 4 must be successful; otherwise, there is no way for the record to be selected anyway. The only way for a “tentatively unselected” account to return to “tentatively selected” status is to have a successful test on a “linked OR” condition; since we’re dealing with an “AND” connector in line 5, the test will be bypassed, and the establishment will not be selected. If the latitude check was “true”, but the tax rate does not exceed the sought limit, then the micro record will be demoted to the “tentatively unselected” status; when both tests are true, it remains “tentatively selected”.


Line 6 is checking whether the county code is in MSA 01234 during the prior (“3”) quarter. It, too, is connected with an “AND” condition, so the only way to keep a record tentatively selected is for this test to be true as well. If the establishment is outside of the MSA, it degrades to tentatively-unselected status, which will keep it out of the output file, delimited file, group, or report. If we were to restate lines 4 through 6 as a single, integrated condition, we could write it as, “If the latitude is not between 41 and 43 degrees, and the prior-quarter tax rate is above 2.5%, and the prior-quarter county is in MSA 01234, then select the record.” Establishments that are either outside the 2-degree latitude range, or have a lower tax rate, or are outside MSA 01234 will not be selected, unless they can meet the final condition, which follows.


Line 7 stands on its own, separated from the other checks by the “OR” at the beginning of the line. This statement is looking for a post-office box number contained in the first street address line of the physical location address block (i.e., “PL-A1”); this condition is a “no-no” in physical address data, but it can be missed when the P.O. box number is anywhere except at the very beginning of the street address line (for instance, a physical address of “123 Main Street P.O. Box 275” would be ignored by system edits, since “123 Main Street” is legitimate. The use of the parentheses (“()”) in the comparison code could appear as “CT” instead. The sought characters (“P.O.”) declare four characters that must be found somewhere in the scanned field in order to select the record. As noted earlier, this line will only need to be checked at all if the previous two sets of conditions (in lines 1-3 and in lines 4-6) were false. Line 7 could be restated in a description such as: “If ‘P.O.’ appears somewhere in the PLA street address, then select the record.” 


Note: This selection method does not include a single-character value, such as the MEEI, an employment or wage indicator code, or status code; these fields could have multiple values checked.  For instance, a reported month-2 employment indicator could include a current-quarter Month-2 indicator of “ ”, “C”, “L”, “R”, or “S”, through the format of “MON2I  C =   ,C,L,R”; this looks for any of the selected Month-2 indicator values for the current quarter that would be the same as one on the list.  If it has a not-equal (“<>” or “NE”) specification, then it will look for any Month-2 indicator values other than the ones specified (e.g., A, D, E, H, etc.).


Related Links

Retrieved from "http://qcewsupportapps.com/index.php?title=05_job_040d_parameters_screen&oldid=4171"