This article details syntax related to making expressions in Solver Cloud and Private Host. This article will help you better understand the syntax required to transform your data when coming into Solver.
Note: Complex aggregation is not supported in the Expression Builder. Major data alteration should be handled in the source system.
Syntax Tutorial
Solver integrations support limited Excel “IF statements” in the Expression Builder. A complex IF statement such as nested IF, IF(OR), IF(AND) may not work unless it contains proper syntax. Below are some examples of accepted formulas along with the translation from Excel to Javascript.
- Expressions within Expression Builder need to be in Javascript Functions syntax.
- The Expression Builder is case-sensitive.
- i.e. if source field is [Payment], using [PAYMENT] will not return data.
IF Statement
? = THEN
: = ELSE
Example
- Excel: IF([Account]=’40010′ , ‘Revenue’ , ‘Other’)
- Cloud: ([Account]=’40010′) ? ‘Revenue’ : ‘Other’
Nested IF Statement
? = THEN
: = ELSE
Example
- Excel: IF([Account]=’40010′ , ‘Revenue’ , IF([Account]=’60010′ , ‘Expense’ , ‘Other’)))
- Cloud: ([Account]=’40010′) ? ‘Revenue’ : ([Account]=’60010′) ? ‘Expense’ : ‘Other’
IF Statement Combining with (OR…)
|| = OR
? = THEN (Take note of parentheses in the Cloud formula)
: = ELSE
Example #1
- Excel: IF(OR(LEFT([Account Number] , 1)=’2′ , LEFT([Account Number] , 1)=’3′ , LEFT([Account Number] , 1)=’4′) , -1*[Amount] , [Amount])
- Cloud: (LEFT([Account Number] , 1)=’2′ || LEFT([Account Number] , 1)=’3′ || LEFT([Account Number] , 1)=’4′) ? -1*[Amount] : [Amount]
Example #2
To replace cells in condition A or B with a different value, otherwise, keep the original value
- Excel: IF((OR([Entity]=’SAS’ , [Entity]=’sus’)) , ‘CORP’ , [Entity])
- Cloud: IF(([Entity]=’SAS’ || [Entity]=’sus’) , ‘CORP’ , [Entity])
IF Statement Combining with (AND…)
&& = AND (Take note of parentheses in the Cloud formula)
? = THEN
: = ELSE
Example
- Excel: IF(AND(LEFT([Account Number] , 1)=’2′ , right([Account Number] , 1)=’3′ , -1*[Amount] , [Amount])
- Cloud: (LEFT([Account Number] , 1)=’2′ && Right([Account Number] , 1)=’3′ ? -1*[Amount] : [Amount]
Note: If copying and pasting formulas directly from this KB does not work, the KB might have converted the single quotes to apostrophes. Replace the apostrophe with single quote should resolve this issue.
Expression Samples
| Description | Source Field Example | Source Data Format | Expression |
|---|---|---|---|
| Convert date to YYYYMMDD | PostingDate | yyyy-mm-dd hh:mm:ss yyyy-mm-dd | text([PostingDate],”yyyymmdd”) |
| Covert date to YYYYMMDD + add X months | PostingDate | yyyy-mm-dd hh:mm:ss yyyy-mm-dd | if([PostDate]=’06/01/2019′,’20190901′,text([PostDate],’yyyymmdd’)) dates referenced in this example will need to be updated to fit your data |
| Add date to a YYYYMM field | Period | yyyymm | left([Period],6)+’01’ concatenate([Period],’01’) |
| Add hours to a dateTime field | CreatedOn | yyyy-mm-dd ss:mm:ss | new Date(Date.parse([CreatedOn]) + (N*60*60*1000)) where N represents the number of hours to add/subtract (24hr clock) |
| Add leading 0s | Employee | nvarchar varchar int | right(‘000’+[Employee],N) ‘000’ represents the leading 0s, it can be modified to fit your organization practice N represents the number of characters need to be included in the Employee codes |
| Trim | RoomTypeDescription | nvarchar | [RoomTypeDescription].trim()).toUpperCase() |
| Source data is converted to Scientific Notations | any numeric source value | numeric | parseFloat([Monthly_Amount]).toFixed(16) |