Create a Custom column and overlay data into it

In this post we will show you how to make a custom column, and how to overlay data into that column. Lets say that you want to have a column which shows the time zone for items in your case. The time zone is shown on date fields, but there is no standalone column that is specific to time zone in Intella. In this case you can make a custom column for the time zone, then overlay the time zone information from one of the date fields into this column.  

The first step is to get the time zone information from a date field. In this case we will use the Primary date field.

Note: Primary Dates are calculated based on values stored in Intella columns. Different file formats populate this field differently. E.g. sometimes the dates are derived from the Raw Data field, and sometimes it comes from the item's property field etc. 

Steps:

First, create a CSV export (or a load file) containing these two columns:

1. Item ID - This is our reference for which records we will update when we load the overlay.
2. Primary Date - We will retrieve the time zone information from this data.

The exported Primary date data will contain the full date format (e.g. date + time + time zone). Open this export in a text editing tool (e.g. Excel, advanced text editors, etc.).
In our example we'll edit the file using Excel. First, the Text to Columns function has to be used in the Data tab to split the full date and time in the cell to separate columns.

Then, remove the date + time portion from the Primary date column. E.g. in Excel you could do this by first using the 'Text to columns' feature on the Primary date column to convert the data into four separate columns. 

Now delete the date, time and am/pm columns to only leave the Item ID column and the time zone column. Note that you will need to name the time zone column as it will not have a name (e.g. We simply named it "Time zone"). Save this file as a .csv file.

Now you'll need to create a new Custom Column of type "TEXT" within Intella.

For Intella Professional, go to File > Custom Columns to open the Custom columns wizard:

1. Click on the New button to create a new column.
2. Name the column. In this example we have named it "TZ Only". You can add a description in the Description field if you wish.
3. Set the Type field to Text.
4. Leave the Extract data field empty and click "OK" to create the custom column.

The final step is to overlay the time zone data into this new custom column.

In Intella Professional,go to File > Import Load File. 

In Intella Connect and Investigator, go to Sources > Add New and select Load File.

The process below is presented using Intella Professional, but the process for the other products (Connect and Investigator) is almost identical.

1. Change the Import operation to Overlay.
2. Path to the .csv file with the Item ID and Time zone data.
3. Everything else can be left as default. Click on the Next button.

Do the following on the "Map Fields" screen. Note there is an image example below that includes all four steps.

1. Set the Identifier field and type to Item ID.
2. Select the Time zone field on the left, then scroll down the Intella columns list on the right and double click on the TZ Only field. This will move the field to the Time zone field on the left. I.e.
   
   
3. We have a function to check for errors. This should be done to make sure there are no errors before loading the overlay.
4. If you have no errors at Step 3, click on the Import button to import the overlay.

There should be no errors reported in the summary window.

The new TZ Only column will be to the far right of the columns in the Details table. You can move this column to where you want it by clicking and dragging. If this column is not shown in the Details table, you can add it using the Column selector.

Here we can see that the time zone from the Primary date is in our new custom column - TZ Only.