Working with a table containing FNF

You might see some table cells containing the string FNF (field not found). This section explains the meaning of FNF and how various operations work with records containing FNF.

When FNF occurs in a table cell

The following list describes the scenarios where you see FNF in a table cell:

  • A row does not have a value for the column. For example, a column named EmployeeID has a value for all rows except row 1. The value in the cell on row 1 in the EmployeeID column is shown as FNF.

    NOTE: Having an empty string for a column is not the same as having no value. For example, in a JSON file, row 1 is {a: 1, b: ""} and row 2 is {a: 2}. In row 1, the field b exists and contains an empty string. In row 2, no value exists for the field b. As a result, row 2 shows FNF for the b column.
  • You change the data type for a column but the data in a cell cannot be changed. Suppose you change the data type of the EmployeeID column from string to integer. If the EmployeeID in row 2 is NA, it is changed to FNF because the string NA cannot be cast into an integer.
  • A table is empty. The following examples describe the scenarios where an empty table is created:

    • No erroneous rows are found when you create a table through a Map or Group By operation and specify that the table contains only erroneous rows.
    • If you filter a nonexistent value, the resultant table is empty with FNF in its cells.
  • The result of a Map function is undefined. For example, when the div or mod function uses a zero as the divisor, the result of the function is FNF.

Filtering and excluding FNF

You can use a cell option to filter or exclude FNF, just as you would other values.

Sorting a table by a column containing FNF

If a table contains a column with FNF, sorting the table by this column results in a table that excludes the rows with FNF. For example, if row 1 contains FNF in the EmployeeID column, after you sort the table by EmployeeID, the resultant table does not include row 1 of the table before the sort.

Do not sort a table by a column that contains FNF in all rows. Doing so results in an error message because the result of the sort would be an empty table.

Standardizing data in a column with FNF

One of the purposes of cleansing and standardizing data is to ensure that all data in a column is of the same type. If a column contains FNF, you can map FNF to a value that is useful for future operations. Mapping FNF to another value also enables a sort to include all rows in the resultant table. The following example illustrates how you can standardize data in an integer column having FNF cells.

To change all occurrences of FNF in this column to an integer, follow these steps:

  1. Start the Map function in the EmployeeID column to display the MAP panel.
  2. Select the exists function in the conditional category.
  3. Accept the resultant column name (EmployeeID_exists).
  4. Click MAP. The EmployeeID_exists column shows true if EmployeeID is an integer and false if EmployeeID is FNF.
  5. Start the Map function in the EmployeeID_exists column to display the MAP panel. The condition to test field is shown as $EmployeeID_exists.
  6. Select the if function in the miscellaneous category.
  7. For the value returned if condition is true field, click the EmployeeID column header. The field now displays $EmployeeID.
  8. For the value returned if condition is false field, type a value to which you want to map FNF. For example, you can enter a negative integer such as -1.
  9. Accept the resultant column name (EmployeeID_exists_if).
  10. Click MAP. The EmployeeID_exists_if column shows the original integer in EmployeeID if one exists. For each row containing FNF in the EmployeeID column, the cell displays -1.

    Now the EmployeeID_exists_if column is identical to the EmployeeID column except that FNF is replaced with an integer (-1). If you sort the table by this column, all rows are displayed after the sort.

NOTE: The string or numeric value you map FNF to depends on your specific operation. The exact Map function used for changing FNF to another value might be different from the one described in this example.

Counting occurrences of FNF in a column

Use the Profile option in the column drop-down menu to count the occurrences of FNF. In the Profile modal window, FNF is shown as null. The bar graph for null is displayed in red. For more information about the Profile modal window, see Counting occurrences of unique values in a column.

Go to top