The 21st Minute


Blog Image

Tech Tip: Transforming Your Data

We are inundated with data on a daily basis. Here are a few time-saving tips for transforming your raw or jumbled data into useful information.

Adding Days to Your Date Fields:

One of the functionalities that we encounter with users is the need to add days to your date fields. In IDEA, we can do this by using the following syntax. Making sure that the date field is stored as a date the following equation will allow you to add days:

@jtod(@dtoj(DATEFIELD) + NumberOfDays)

This functionality will convert your day to Julian, which allows you to add the number of days needed, and then convert the day back to a date field to store in a new field.

Compare Data from the Above/Below Record:

In some cases, the data needed to be compared can be located in the row above or below and is needed to analyze your result. There is a functionality in IDEA where you can access these data.

Note that you would need to Sort the data using the proper fields to verify that the rows above and below are in the correct sequence. By appending a new field using this functionality you can move the data to the records where the comparison needs to take place.

The next value would give you the data from below and the previous value from the above record.

Note that in some cases these would need to be incorporated into an if to avoid capturing incorrect data. For instance, if you need to grab the amount, only if the Vendor Numbers are the same, then your equation would look like this:

@if(VENDOR_NUMBER = @GetNextValue(“VENDOR_NUMBER”), @GetNextValue(“AMOUNT”), 0)
The 0 would be provided if the VENDOR_NUMBER does not match between rows.

CaseWare IDEA , Tech Tip

Posted By

By Ricardo Murillo

Related Posts
Wayfair Woes One Year Later
Jul 24 The rise in online shopping has sharply impacted state sales tax revenue. Rather than increasing the tax rate, states are setting parameters to define nexus, wh...
Importing Date Fields
Jul 24 There are times when you import a date field and it just does not work out as expected. Here are ways to address the problem before or after the import.  ...
Tech Tip: Understanding Join and Visual Connector
Jun 19 Using IDEA’s Join and Visual Connector features can help you search for matches and correlations between different data sets, but they are often confused with...

This website has been designed for modern browsers. Please update. Update my browser now