Proto Software

Description

OLD CALCULATION METHOD - This is using the Attribute Calculator and Formula in the old-school way to calculate columns. It may still be of interest though if you're learning about the Attribute Calculator...

This module provides a convenient way to do basic processing of an Attribute using the data in a single Entry. There is a button which launches the Formula Property Panel to give quick access to editing the formula.

You can select whether to process the data in place or into a new Attribute.

To change the processing to be more sophisticated such as summing two entries, calculating changes over two sequential entries, etc. - you can always edit the Formula as needed and then go to the Attribute Calculator to redefine the ranges for input.

Some common functionality could be...
* Replace text
* ==SUBSTITUTE(Value, "replaceThis", "with this")==
* ==This is really handy if you have unclean data come in. For example, you get market caps with 500M which are read as text instead of numbers. You could do SUBSTITUTE(value, "M", "000") to clean the data. Similarly you could need to remove some text like commas or something. You can use SUBSTITUTE(value, "remove this", ""). The double quotes mean replace it with nothing.==

* Get the first part of the text
* Left(Value, howManyDigits)
* ==If you put in LEFT("1234", 2) the output will be "12". If you know the Country code is the first 3 letters of a Trade ID, you can use this to pull out just that part of the text.==

* Get any part of the text
* Mid(Value, whereToStart, howManyDigits)
* ==If you want to get only the second character in the Text you would use MID(Value, 1,1). The "whereToStart" begins at 0. If you want to get the right-most part of the data use MID(Value, Len(Value) - howManyDigits, howManyDigits). Len(text) returns how long the text is.==

* change the CASE of the data
* Lower(Value) and Upper(Value)
* These change the case to lower case and upper case respectively.

* Find if text appears in the data
* ==FIND("findSomeText", Value, whereToStartLooking)==
* ==This will return a number like 3 if you entered FIND("def", "abcdef",0) which means look for "def" in the string "abcdef" starting at character 0 (the beginning). If you did FIND("def", "abcdef",4), it would return -1 which means it didn’t find the text.==

* Shift a number randomly by +- 2%
* ==value*(0.98+0.04*rand())==
* You can use the Rand() function to get a value between 0 and 1.

* Round a number
* Round(Value,2)
* This will round a number to 2 decimal places. If you want to round to the nearest 10,000 for example, use: Round(Value/10000,0)*10000

* Date formatting, addition, etc.
* Go to the Property Panel and click "Browse Functions"
* There are many useful date functions in the formula list.

Categories

• Just For Fun
• Proto Tutorial Tools