Reformatting Data

By reformatting a field, you can create a more readable, and more usable format for analysis. In some cases, you can also reduce space. For example, convert a date field to a numeric field. You can reformat fields within the ElastiCube using a custom SQL expression.

Note:

When using floating points data types (Real, float, double), some decimal numbers may be rounded. If you need full decimal accuracy, try using BigInt field types.

Numeric Representation of Date Fields

Business Case

Create a "Date" table using a numeric representation of a Date field instead of the Date field itself to improve query performances. In addition, a numeric representation of a Date can provide the ability of filtering the data by time range.

Solution

To convert a Date ("4/21/2012 12:36:56 AM") to an integer (20120421), use the following syntax:

10000*getyear(Date)+100*getmonth(Date)+getday(Date)

To get the hours and minutes too, use a bigint numeric representation. So, "4/21/2012 12:36:56 AM" will become: 201204210036, using the following syntax:

tobigint(100000000*getyear(DateTime)+getmonth(DateTime)*1000000+getday(DateTime)*10000+100*gethour(DateTime)+getminute(DateTime))      
                                                    