By reformatting a field, you can create a more readable, and more usable format for analysis, as well as less space consuming in some cases. For example, convert a date field to a numeric field. You can reformat fields within the ElastiCube using a custom SQL expression. An example:
Creata 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.
To convert a Date (“4/21/2012 12:36:56 AM”) to an integer (20120421), use the following syntax:
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:
Hey! Was this article helpful?
Questions? Ask the community.