Better Business Through Data Analysis & Monitoring My Account

Importing Date Fields

Tech Tip to Steps for Dealing with Date Fields

A common problem with importing any file into IDEA is dealing with date fields.  The import assistant reads the field and does its best to determine what kind of data is being imported and what type of field to create.  If there is anything in the field that doesn’t look like a date, it will either create a Numeric or Character field.  Fortunately, there are ways to either correct this at the time of import or in the database after the file has been imported.

Correcting the Date Field When the File is Imported

Certain types of imports allow users to specify field types during the import process. For example, when you import a text file, the field is created and the data type is assigned. In the example below, the Import Assistant wanted to import this date field as a Character field.

Import Assistant - Field Details Dialog Box

To correct that, select the field; then use the drop down next to “Type:” to change it to a Date field. When the type is changed to Date, you will be prompted to enter a date mask.

Import Assistant - Field Details Date Mask Dialog Box

In this case, the mask “YYYYMMDD” was used because it matches the format of the data in the file.

Report Reader imports also allow you to change the field type during the import process. When importing the same file as above, Report Reader identified that field as a Numeric Field.

Screenshot of Where to Edit Field Type Info in Report Reader

To change the type to a date field, select the field and use the drop down next to “Type” to change it to a Date field. As before, you will be prompted to enter a matching mask for the field. Update the mask and save the layer.

It’s important to note that some import types do not allow you to specify the field type. Most commonly, Excel imports read the data in the field and make a best guess as to what it is that you are trying to import. Even if the cell is listed as Date in Excel, the Import Assistant may read the incoming data as a Character or Numeric field. In those cases, it’s usually best to go ahead and import the file and change the field type in the resulting database.

Changing the Field Type in IDEA

If you’ve already imported the file and find that your date field is not using the Date field type, you can still make that correction in IDEA. First, click the Data tab then click Modify.

Modify Field Function on Data Tab

The Modify Field Window will open. Use the drop down next to “Field type:” to change the field to Date. Enter the appropriate date mask in the Parameter field.

Dialog Box for Modify Field

Then click the OK button to make the update

In all cases, the data must be formatted consistently to use the method of changing the data type to date. For example, in some files, dates can be listed as 1/15/2017 or 1/4/2017 or 10/15/2017. The “mm/dd/yyyy” format is not consistent. In this situation, the field should be imported as a character field and reformatted inside IDEA using @functions.

Reformatting Dates in IDEA

Date masks have been mentioned multiple times in this article. It’s very important to note that the date mask you enter must exactly match the format of all the dates in your field for the update to work correctly. If you update the field and you see red errors listed instead of a date, the most likely cause is that the mask doesn’t match what is in the field.

Date Field Errors Due to Field Mask Not Matching Exactly

The mask will need to be updated. Make sure to include any slashes, dashes, spaces or any other special characters in your date.

It’s not uncommon for a month to be listed as an abbreviation instead of a number (JAN for January, MAR for March, etc…). In these cases, you can use three M’s for the month. For example, the mask for 17 JUL 2016 would be “DD MMM YYYY”. In this example, IDEA is smart enough to know that JUL is July.

When dates are not formatted consistently in the same file, a single date mask will not work for all of the dates. For example, the date mask for 10/20/2015 is “MM/DD/YYYY”. This same mask won’t work for a record with a date of 3/10/2015. The problem is that the second date only has a single digit for the month instead of two indicated by the mask. If you had both of these dates in a field, that mask would work on the first one, but would return an error on the second. To format dates in this case, the best thing to do is append a new Date field and write an equation to convert the date. The following equation would test the length of the first and second segments of the date and make updates as necessary.

@Ctod(@if(@Len(@Split(@Alltrim(DATE),"","/",1))=1,"0"+@Split(@Alltrim(DATE),"","/",1),@Split(@Alltrim(DATE),"","/",
1))+"/"+@if(@Len(@Split(@Alltrim(DATE),"","/",2))=1,"0"+@Split(@Alltrim(DATE),"","/",2),@Split(@Alltrim(DATE),"","/",
2))+"/"+@Split(@Alltrim(DATE),"","/",3),"MM/DD/YYYY")

Note that this equation will work in this instance, but may need to be edited to account for variations in your date fields.

If you have questions about this or other IDEA functions, please give our help desk a call at:
888-641-2800 Option 4
Or email us at helpdesk@audimation.com

Print

Number of views (4216)/Comments (0)

Brad Newman

Brad Newman

Other posts by Brad Newman
Contact author

Please login or register to post comments.

Name:
Email:
Subject:
Message:
x
Global Chemical Company Automates Risks Identification with CaseWare IDEA
14 September 2018

Global Chemical Company Automates Risks Identification with CaseWare IDEA

Global corporation used IDEA to standardize & develop an automated process to comply with anti-corruption policy.

Read More

Telecommunications Provider Turns Two Days of Work Into 20 Minutes with CaseWare IDEA®
14 September 2018

Telecommunications Provider Turns Two Days of Work Into 20 Minutes with CaseWare IDEA®

Auditors Analyzing Hundreds of Millions of Records to Track Buying and Usage Patterns

One of the world’s largest telecom companies' internal audit team leverages tech to analyze trends, tendencies & patterns within customer & financial data.

Read More

Exploring IDEA's Built-In Power of Python
20 August 2018

Exploring IDEA's Built-In Power of Python

IDEA Tech Tip

With the release of version 10.3, IDEA created a buzz with its implementation of the Python scripting language, thereby taking a huge step towards having a more robust coding environment.

Read More

Sharing IDEA Data Using IDEA's ODBC Driver
18 July 2018

Sharing IDEA Data Using IDEA's ODBC Driver

IDEA Tech Tip

Read More

Oncor Achieves Operational Efficiencies with CaseWare IDEA
25 June 2018

Oncor Achieves Operational Efficiencies with CaseWare IDEA

Oncor's internal audit team needed to acquire years of data from different environments and formats to help improve processes and efficiency levels.

Read More

Analyzing the Past to Predict the Future with Trend Analysis
18 June 2018

Analyzing the Past to Predict the Future with Trend Analysis

In business, predicting the future is never an easy task. But IDEA can help as long as you have the right data, and Trend Analysis can help.

Read More

2018 IDEA® User of Excellence Award Winner Selected
15 June 2018

2018 IDEA® User of Excellence Award Winner Selected

Jennifer Girard, CIDA with American Red Cross Recognized for Exceptional Use of Data Analytics

Jennifer Girard, Sr Auditor with the American Red Cross is the recipient of the 2018 US IDEA UoE Award. She utilized IDEA to assist Finance Department during a natural disaster.

Read More

The Rules of Nature and Audit
6 June 2018

The Rules of Nature and Audit

What’s missing is often as important as what’s present. The rules of nature apply very well to audit if you consider, “Everyone knows it, but do you test for it?”

Read More

Using IDEA to Import QuickBooks General Ledger Data
4 June 2018

Using IDEA to Import QuickBooks General Ledger Data

The General Ledger is typically the starting point to an expanse of detailed information, see a step-by-step guide on how to get a readable version in idea.

Read More

New Features in IDEA 10.3 Part II
10 May 2018

New Features in IDEA 10.3 Part II

IDEA Tech Tip

In a recent article, we introduced the improvements to Stratified Random Samples and Python integration added in IDEA 10.3. But that’s just a small part...

Read More

Cargo Company Swaps Programming-Intensive Data Analytics Software for CaseWare IDEA
10 May 2018

Cargo Company Swaps Programming-Intensive Data Analytics Software for CaseWare IDEA

Auditors Achieve More Success in First Year with IDEA Than 5+ Years with Other Tool

With the entire audit team up and running using data analytics, they are finding new ways to integrate IDEA into other areas of the business and automate repeatable processes.

Read More

Maximizing Your Environment - Global Variables
8 May 2018

Maximizing Your Environment - Global Variables

Consider scope to be a building in which people work. The tools required to accomplish their tasks will of course be housed in that building, but in scripting it’s important to determine which tools need to be shared among the buildings.

Read More

Tips on Acquiring Data
8 May 2018

Tips on Acquiring Data

An IDEA User Shares Frequent Practices

When working with rows and columns of data to spot suspicious behaviors, fraud and errors can look very similar to one another. While Yeriazarian often uses Excel as an interim format for data, he also understands there are several avenues to navigate before getting the right data.

Read More

Identifying Churn in AR
19 April 2018

Identifying Churn in AR

IDEA Tech Tip

Customer churn (customer attrition, turnover, or defection) is when a customer decides to stop engagement with your company. IDEA can help easily identify “churn” in accounts receivable by following these steps...

Read More

RSS