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.

@GetNextValue(“FIELDNAME”)
@GetPreviousValue(“FIELDNAME”)
 
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.