Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
These functions can only be used in field lists for Load statements with a group by clause. We use aggregation function in chart to group the values of the chart.The basic Aggregation function is
Returns the sum of expression over a number of records as defined by a group by clause. If the word distinct occurs before the expression, all duplicates will be disregarded.
Example:
Load Month, sum(Sales) as SalesPerMonth
from abc.csv group by month;
min( expression[, rank] ) Returns the minimum numeric value of expression encountered over a number of records as defined by a group by clause. Rank defaults to 1 which corresponds to the lowest value. By specifying rank as 2 the second lowest value will be returned. If rank is 3 the third lowest value will be returned and so on.
Examples:
Load Month, min(Sales) as SmallestSalePerMonth from abc.csv group by Month;
Load Month, min(Sales, 2) as SecondSmallestSalePerMonth from abc.csv group by Month;
max( expression[, rank] ) Returns the maximum numeric value of expression encountered over a number of records as defined by a group by clause. Rank defaults to 1 which corresponds to the highest value. By specifying rank as 2 the second highest value will be returned. If rank is 3 the third highest value will be returned and so on.
Examples:
Load Month, max(Sales) as LargestSalePerMonth from abc.csv group by Month;
Load Month, max(Sales, 2) as SecondLargestSalePerMonth from abc.csv group by Month;
only(expression ) If expression over a number of records, as defined by a group by clause, contains only one numeric value, that value is returned. Else, NULL is returned.
Example:
Load Month, only(Price) as OnlyPriceSoldFor from abc.csv group by Month;
mode(expression ) Returns the mode value, i.e. the most commonly occurring value, of expression over a number of records, as defined by a group by clause. If more than one value is equally commonly occurring, NULL is returned. Mode can return numeric values as well as text values.
Examples:
Load Month, mode( ErrorNumber ) as MostCommonErrorNumber from abc.csv group by Month;
Load Month, mode( Product ) as ProductMostOftenSold from abc.csv group by Month;
firstsortedvalue ([ distinct ] expression [, sort-weight [, n ]]) Returns the first value of expression sorted by corresponding sort-weight when expression is iterated over a number of records as defined by a group by clause. Sort-weight should return a numeric value where the lowest value will render the corresponding value of expression to be sorted first. By preceding the sort-value expression with a minus sign, the function will return the last value instead. If more than one value of expression share the same lowest sort-order, the function will return NULL. By stating an n larger than 1, the nth value in order will be returned. If the word distinct occurs before the expression, all duplicates will be disregarded.
Example:
Load Customer,
firstsortedvalue(PurchasedArticle, OrderDate) as FirstProductBought from abc.csv
group by Customer;
just try to use all the functions.
