How to Manage Columns with ADA

Introduction

ADA (Audit Data Assistant) allows you to manage column settings after you’ve imported your data so you can change things like column names and data types.

Using the Column Management Dialog

The Column Management dialog is where you change settings for the columns in your data files. Its settings are presented in grid form:

Column Name. To change the name of a column, double-click name you wish to change. It will then be highlighted in dark blue. Type the new name and press Enter.

Data Type. Sometimes changing the data type of a column is necessary. Common examples of this are IDs that import as numbers instead of characters and dates that import as numbers or character. To change a column’s data type, click the dropdown and select the desired type.

Formula. Clicking a Formula cell will select it and allow you to type a criteria formula into the cell. Alternatively, you can double-click a Formula cell to open the Criteria Editor dialog box. For a more comprehensive overview of Criteria, see How to Write Criteria in ADA.

Formulas mostly apply to calculated columns, but they can also be used to specify the format for datetime columns. For example, if you wanted to calculate a unit price for each product in a data set, you could create a column called UNIT_PRICE and enter a formula like TOTAL_PRICE / NUMBER_OF_UNITS where the names in the formula are names of the appropriate columns in your data.

If changing the data type to Date/Time, one can type dialog into the Formula section and click OK to get a dialog box for entering common Date/Time formats. The formats can be subsequently edited to suit. If no Formula is entered when changing the data type to Date/Time, ADA will guess at the appropriate format. If it cannot be determined for certain, the dialog will be presented. Alternatively, the user can provide a format according to Python strftime format as discussed here:

https://docs.python.org/3/library/datetime.html#strftime-and-strptime-behavior

Add. The Add button inserts a row into the grid so you can create a new column. By default, the new column is given the name NEW_COLUMN_X with X being a number representing the column count. Replace this name with the name you desire for the new column, select its data type, and provide your formula.

Delete. The Delete button deletes the chosen column from the grid. It will ask you if you’re sure you want to delete the column. If you find that you’ve accidentally deleted a column from the grid, just click cancel. Your column will not be removed from the data file.

Changes made to column settings in the grid are strictly local and are NOT applied to the underlying data file until you click OK. If you change one column’s name while adding a new column and deleting another column, nothing will happen to the underlying data if you then click Cancel.

Formats Supported by Column Management

ADA’s Filter utility allows you to modify column settings of Parquet data. Parquet is the format ADA uses for storing data files.

Questions

If you have questions about ADA software or you would like to know about purchasing custom ADA analytics, wonderful! Please call us at (864) 625 – 2524, and we’ll be happy to help.