Fort Myers-Naples, FL
VeraData Example: Removing Records
VeraData, as most of you know by now, relies heavily on data processing, blending, and ETL. This specific project from one of our top clients focused on data processing from an SQL request.
The issue: The output file contains more than 300 empty fields, but they’re not totally empty, they contain words “Null” and “Nul” instead of an undefined value. In this case, we can’t remove these fields, because they aren’t empty and contain String variables. (See Example Below)
We used this combination of tools to resolve the issue: Multi-Field Formula, Auto Field tool, and Dynamic Select tool.
In the first step, we used the Multi-Field Formula tool to make a condition for replacing data for all columns in the file. You can see the configuration and formula in the screenshot below:
This tool is replacing the String variable, which lies in the current diapason, on an unidentified variable “NULL.” After that, we used the Auto Field tool to recognize the type of data and the record length.
Below, we can see the results from the Auto Field (above) in the Select Tool. The columns that didn’t contain any information at all were assigned the type “Bool.” The column “Field 4” contained data and unidentified values, so the data type is identified as a String.
The last step in the process is to remove the columns that don’t contain any data at all, by using the Dynamic Select tool. The Dynamic Select tool sorts the data by type of field, by name, by length, etc. In this case, we used sorting by type. We knew what type was assigned for the empty records (Bool), so we removed those from the list by unchecking the box. You can see configuration on the screenshot below.
Now the output file looks like this: (Below) As you can see, all of the records that contained the words “Null” and “Nul” now contain the unidentified variable “Null” instead, and all empty columns that didn’t contain any data at all were removed from our file.
Below is the workflow:
You can use this example for removing different types of records, using the necessary formulas for entire columns or specific groups, depending on what you’re trying to accomplish.
Please feel free to reply to this post with questions or comments, we’d love to hear from you!
