Helical Insight provides user the flexibility to apply database functions on different data formats like Text, numeric, date, time, date-time etc. Following are the steps to apply database functions. In the below blog we have shown the steps to apply a single function. But you can also apply multiple nested functions also like at outside convert to uppercase, within that another which is extract monthname etc. So multiple nested functions of different types can also be applied one within another.
Step 1: After getting the columns in the selection area, click on the downwards arrow on the column and then click on ‘More’.
Step 2: Users would then get two options; Quick and Advanced. In the Quick mode, user can quickly select the function from the list for that particular data type and apply as shown below:
Note: The functions in the list vary for different databases.
In advanced mode, users can apply functions of other data types to the data type selected. Following are the steps to do the same:
Step 3: Click on the Advanced tab and select the data type of which the functions are to be applied.
Step 4: Select the function that is to be applied by double-clicking on it. It will then appear in the bottom as shown in the image below:
Step 5: Now drag the column in the empty box below the selected function and click on save.
Step 6: Click on generate report.
In case if you would like to edit or delete the applied Database function you can refer to this blog How to Delete Database Function.Type a message
Uses of some Numeric data type functions:
- Average: Shows the average value of the data
- Round: Round off the values
- Power: Displays number raised to the power of another number
- Sqrt: Displays square root
- Mod: Displays the reminder value
Uses of Text data type functions:
- Concat: Used for joining multiple text.
- Proper: Used to convert first letter of text into upper case and rest into lower case.
- Lower: Converts the text into lower case.
- Upper: Converts the text into upper case.
- Insert: Inserts the string at any position.
- Trim: Removes the space from beginning and ending of the string.
Use case: Let’s try to extract year from a date-time data format. For that, go to More options by clicking on the downward arrow.
Now go to the advanced mode and select data type as date. Double click on the ‘year’ function so that it appears below in the functions applied box.
Select the column on which the function is to be applied and drag it under the brackets of year function as shown below:
Click on save and generate report. You can now see the result in which the date-time format is converted into year:
Learn how to remove applied DBfunction here : https://www.helicalinsight.com/delete-applied-database-function/
Learn the various DBfunctions for handling unstructured data like array, JSON etc here : https://www.helicalinsight.com/database-functions-unstructured-data/
Helical Insight even allows to add new custom function in the DBfunction list, learn here : https://www.helicalinsight.com/adding-a-database-function/For More Info, Contact us at firstname.lastname@example.org