The Linq query syntax provides the ability to execute more advanced query syntax in both the Select and Where clauses or the query. Supported operations are:
- All standard mathematical expressions are supported (+, -, *, / etc.)
- Select (@FieldA * @FieldB / 100) will multiply FieldA by FieldB and divide the result by 100.
- Brackets are supported, ‘(‘ and ‘)’ to group related portions of an expression.
- Since Ling queries are based on the Microsoft .Net libraries advanced mathematical functions are also support (e.g. Select ((Math.Log(@MyNumber) as TheNumberAsLog)
Note: advanced functions must be preceded by their .Net library name (e.g. Math)
Nathean Analytics provides support for string manipulation in the following ways
- String concatenation: @Currency + " " + @MyField as Team
- Substrings: “(@MyField.Substring(3))” would return the string from the 3rd character position. * Empty String Checking – e.g. String.IsNullOrEmpty(@MyField)
Nathean Analytics also supports conditional expressions similar to IF statements in other query languages. The format of a conditional expression is:
Condition? Positive : Else
An example of usage is:
@FieldA >= 18 ? “Over 18” : “Under 18”
This reads as: If the value of FieldA is greater than or equal to 18 then return the string “Over 18”, otherwise return the string “Under 18”.
Multiple Conditions can also be checked, for example:
@FieldA < 13 ? “Child” : @FieldA >= 13 && @FieldA < 18 ? “Teenager” : “Adult”
This reads as: If the value of FieldA is less than 13 then return the string “Child”, If the value of FieldA is greater or equal to 13 and less than 18 return the string “Teenager”, Otherwise return the string “Adult”
The Linq engine allows the ability to join across two objects. The syntax is:
(MyObject) .Select(@Name as NameColumn) .Join(JoinObject on @JoinField = @Name)
The syntax dictates that the main object being selected from is called MyObject, MyObject is to be joined to an object named JoinObject by joining the field named ‘JoinField’ from ‘JoinObject’ to the field named ‘Name’ on MyObject.
It is also possible to produce summary result in a Linq query by performing grouping. The syntax is:
(MyObject) .Select(@Name as Name, Count(@Account) as NumAccount) .GroupBy(@Name)
Sum, Average and Count Summaries are supported.
It is possible to group by portions of fields. For instance in dates you may want to group by just the month or year portion of the date. The syntax GroupBy((@TheDate).ToString(“MMM”)) will group by the short month name (Jan, Feb etc.) portion of the date.
Valid groupings on Date fields are:
(@TheDate).ToString("MMM") --Short Month textual format (@TheDate).ToString("MMMM") --Long Month textual format (@TheDate).ToString("ddd") --Short Day textual format (@TheDate).ToString(“dddd") --Long Day textual format (@TheDate).Year --Year Portion of the date (@TheDate).Month --Month Portion of the date (@TheDate).Day --Day Portion of the date
The syntax to order the results by a column(s) is:
(MyObject) .Select(@Name as Name, Count(@Account) as NumAccount) .OrderBy(Name desc)
Note: The values used for the OrderBy Function should be the alias names of the columns as the Order By takes place after the query has been executed (e.g. Name instead of @Name)
In the above example the results are ordered by the ‘Name’ field in descending order. Removing the ‘desc’ portion of the query will sort in ascending order. It is possible to order by multiple columns by separating them with commas. (e.g. .OrderBy(col1, col2 desc).
The base dataset may be transformed into a pivot view by adding a pivot clause to the query. The syntax of the Pivot clause will be as follows:
.Pivot(xAxisField, yAxisField, zAxisField, constantField1, ConstantField2, …)
xAxisField – The field that will appear as the column heading across the top of the table.
yAxisField – The field that will appear to the left of the report as the grouped fields
zAxisField – The field that will be used to produce the summary data. The field used as the zAxisField must be a numeric field, it’s values will be summed.
Any number of additional constant fields may be passed as parameters after the zAxisField parameter. e.g. An object may be passed a parameter as an input, it may be required to display the value of the parameter on each line of the output data. The syntax will be:
.Pivot(xAxisField, yAxisField, zAxisField, :REGION)
If a parameter named region with a value of ‘Europe’ is passed then the value ‘Europe’ will appear as the first value in every row.
(BaseObject) .Select(@Year as Year, @Quarter as Quarter, @Amount as Amount)
Will return results similar to:
If a pivot clause was added to the query such as:
(BaseObject) .Select(@Year as Year, @Quarter as Quarter, @Amount as Amount) .Pivot(Quarter, Year, Amount)
Note: The values used for the Pivot Function should be the alias names of the columns as the pivot takes place after the query has been executed (e.g. user Year instead of @Year)
The following output would occur:
Custom Extension Methods
The following are the list of custom extension methods available in Nathean Analytics
|Recurse||ParentColumnName, ChildColumnName||Will recursively find each record which the child column is a parent of|
|Concat||KeyField, ConcatField||For every record group all the instances of KeyField together into 1 record and concatenate all the values of ConcatField in 1 single comma delimited|