Properties that can be configured for the Filter Preparation Component.
| Property | Description |
|---|---|
| Selected Columns | Select columns for analysis. |
| Filter Condition | Enter the filter condition. |
| Store | Revenue | Profit |
|---|---|---|
| Land Mark | 10000 | 1000 |
| Spencer | 20000 | 4500 |
| Soch | 25000 | 8000 |
| Revenue | Profit |
|---|---|
| 20000 | 4500 |
| 25000 | 8000 |
| Emp ID | Emp Name | DOB | Age | Date of Joining | Date of Confirmation |
|---|---|---|---|---|---|
| 1 | Laura | 11/11/1986 | 25 | 12/9/2005 | 27/11/2005 |
| 2 | Desy | 12/5/1981 | 30 | 24/6/2000 | 10/7/2000 |
| 3 | Alex | 30/5/1978 | 33 | 10/10/1998 | 24/10/1998 |
| 4 | John | 6/6/1979 | 32 | 2/12/1999 | 20/12/1999 |
| Category | Function (Function when applied on the Employee table) | Description |
|---|---|---|
| Date | DAYSBETWEEN | Returns the number of days between two dates. |
| CURRENTDATE | Returns the current system date. | |
| MONTHSBETWEEN | Returns the number of months between two dates.
For example, the new column contains 2,0,2,0 when MONTHSBETWEEN([Date of Joining],[Date of Confirmation]) is applied to the Employee table. |
|
| DAYNAME | Returns the day name in the string format.
For example, the new column contains Monday, Saturday, Saturday, Thursday when DAYNAME([Date of Joining]) is applied on the Employee table. |
|
| DAYNUMBEROFMONTH | Returns the day number
of the particular month.
For example, 12/11/1980 returns 12. |
|
| DAYNUMBEROFWEEK | Returns the day number
in a week.
For example, Sunday =1, Monday=2. |
|
| DAYNUMBEROFYEAR | Returns the day number
in a year.
For example, 1st Jan =1, 1st Feb=32, 3rd Feb=34. |
|
| LASTDATEOFWEEK | Returns the date of the
last day in a week.
For example, 12/9/2005 returns 17/9/2005 |
|
| LASTDATEOFMONTH | Returns the date of the
last day in a month.
For example, 12/9/2005 returns 30/9/2005 |
|
| MONTHNUMBEROFYEAR | Returns the month
number in a date.
For example, Jan=1, Feb=2, Mar=3 |
|
| WEEKNUMBEROFYEAR | Returns the week number
in a year.
For example, 12/9/2005 returns 38. |
|
| QUARTERNUMBEROFDATE | Returns the quarter
number in a date.
For example, 12/9/2005 returns 3. |
|
| String | CONCAT | Concatenates two strings.
For example, CONCAT('USA', 'Australia') returns USAAustralia. |
| INSTRING | Returns true - if the search string is found in the
source string.
For example, INSTRING('USA', 'US') returns true. |
|
| SUBSTRING | Returns a substring
from the source string.
For example, SUBSTRING('USA', 1,2) returns US. |
|
| Math | MAX | Returns the maximum value in a column. |
| MIN | Returns the minimum value in a column. | |
| COUNT | Returns the number of values in a column. | |
| SUM | Returns the sum of the values in a column. | |
| AVERAGE | Returns the average of the values in a column. | |
| Conditional Expression | IF(condition) THEN(string expression/mathematical expression/conditional expression) ELSE(string expression/mathematical expression/conditional expression) | Checks whether the
condition is met, and returns one value if 'true' and another value if 'false'.
For example, IF([Date of Joining]>12/9/2005) THEN ('Employee joined after Sept 12, 2005') ELSE ('Employee joined on or before Sept 12, 2005') |
Use mathematical operators to create formulas containing numerical columns and/or numbers. For example, the expression [Age] + 1 adds a new column with the values 26, 31, 34, 33.
| Mathematical Operators | Description |
|---|---|
| + | Addition operator |
| - | Subtraction operator |
| * | Multiplication operator |
| / | Division operator |
| () | Round brackets or parenthesis |
| ^ | Power operator |
| % | Modulo operator |
| E | Exponential operator |
Use conditional operators to create IF THEN ELSE or SELECT expressions.
| Conditional Operators | Description |
|---|---|
| == | Equal to |
| != | Not equal to |
| < | Less than |
| > | Greater than |
| <= | Less than or equal to |
| >= | Greater than or equal to |
Use logical operators to compare two conditions and return 'true' or 'false'. For example, IF([Date of Joining]>12/9/2005 && [Age] >=25 ) THEN ('True') ELSE ('False') adds a new column with values True, False, False, False.
| Logical Operators | Description |
|---|---|
| && | AND |
| || | OR |