Splits each record in an array into one or more additional records.
Use this function as a way to transform columns into rows.
Version 1
HTTP RequestPOST /ado/v1/Split
Header
| Parameter | Description |
|---|---|
| Ocp-Apim-Subscription-Key | The subscription key you received when you purchased a plan. |
Request Body
Mandatory
| Parameter | Type | Description |
|---|---|---|
| splits | object[] | Each object within the array |
| data | object[] | Array containing one or more single level objects. |
Common
Further Documentation: Common Parameters
| Parameter | Type | Description |
|---|---|---|
| filter | String | A fully functioning SQL based WHERE statement that will filter the outgoing dataset to the records it determines as being included. |
| sortOrder | Object | Sorts the resulting dataset by the criteria provided. The object should contain property names that represent properties contained within the resulting dataset with a value corresponding to the desired sort direction (i.e. ASC or DESC). |
| schema | Object | You can use this parameter to override the inferred schema for properties in the incoming dataset(s). A field will be inferred unless specific explicitly within this object. |
| advancedOptions | Object | Is an object with the following properties. cultureName (String) The specified culture determines the behaviour for aspects related to formatting numeric values and dates. Is extremely important when converting strings to dates, e.g. 05/03/2022 will be treated differently between locales. For more information on the accepted values for this property, please consult the documentation from Microsoft … https://docs.microsoft.com/en-us/dotnet/api/system.globalization.cultureinfo?view=net-6.0#culture-names-and-identifiers isBoolean (string[]) A list of strings that are the names of all properties in the resulting dataset that should be treated as boolean values. By default, the framework treats boolean values as 1 (true) or 0 (false). In order to differentiate the integer as a boolean, the property in question needs to be called out explicitly in this list. |
Examples
Complete
Some larger financial systems (e.g. SAP) will produce an output which needs to be pivoted/split from one record into multiple because that data is stored in a fixed column format.
In this example, amounts are represented in properties LC_## (Local Currency) and GC_## (Group Currency) combined with the given financial period and therefore, needs to be split and assigned a currency type and fiscal period.
For demonstration purposes, the example only has one item in the data array.
Request
{
"splits": [
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 1,
"Month": "Jan",
"Amount": "LC_01"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 2,
"Month": "Feb",
"Amount": "LC_02"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 3,
"Month": "Mar",
"Amount": "LC_03"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 4,
"Month": "Apr",
"Amount": "LC_04"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 5,
"Month": "May",
"Amount": "LC_05"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 6,
"Month": "Jun",
"Amount": "LC_06"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 7,
"Month": "Jul",
"Amount": "LC_07"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 8,
"Month": "Aug",
"Amount": "LC_08"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 9,
"Month": "Sep",
"Amount": "LC_09"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 10,
"Month": "Oct",
"Amount": "LC_10"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 11,
"Month": "Nov",
"Amount": "LC_11"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "LC",
"MonthNum": 12,
"Month": "Dec",
"Amount": "LC_12"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 1,
"Month": "Jan",
"Amount": "GC_01"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 2,
"Month": "Feb",
"Amount": "GC_02"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 3,
"Month": "Mar",
"Amount": "GC_03"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 4,
"Month": "Apr",
"Amount": "GC_04"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 5,
"Month": "May",
"Amount": "GC_05"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 6,
"Month": "Jun",
"Amount": "GC_06"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 7,
"Month": "Jul",
"Amount": "GC_07"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 8,
"Month": "Aug",
"Amount": "GC_08"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 9,
"Month": "Sep",
"Amount": "GC_09"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 10,
"Month": "Oct",
"Amount": "GC_10"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 11,
"Month": "Nov",
"Amount": "GC_11"
},
{
"CostCentre": "CostCentre",
"CostElement": "CostElement",
"FiscalYear": "FiscalYear",
"Currency": "GC",
"MonthNum": 12,
"Month": "Dec",
"Amount": "GC_12"
}
],
"Data": [
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"LC_01": 844488,
"LC_02": 372164,
"LC_03": 685792,
"LC_04": 216365,
"LC_05": 496296,
"LC_06": 133082,
"LC_07": 299170,
"LC_08": 206288,
"LC_09": 432910,
"LC_10": 752285,
"LC_11": 324837,
"LC_12": 788186,
"GC_01": 658825,
"GC_02": 114371,
"GC_03": 5236,
"GC_04": 207685,
"GC_05": 235734,
"GC_06": 139577,
"GC_07": 228516,
"GC_08": 360174,
"GC_09": 374068,
"GC_10": 62140,
"GC_11": 740896,
"GC_12": 946384
}
]
}
Code language: JSON / JSON with Comments (json)Response
[
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 1,
"Month": "Jan",
"Amount": 844488
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 2,
"Month": "Feb",
"Amount": 372164
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 3,
"Month": "Mar",
"Amount": 685792
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 4,
"Month": "Apr",
"Amount": 216365
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 5,
"Month": "May",
"Amount": 496296
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 6,
"Month": "Jun",
"Amount": 133082
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 7,
"Month": "Jul",
"Amount": 299170
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 8,
"Month": "Aug",
"Amount": 206288
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 9,
"Month": "Sep",
"Amount": 432910
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 10,
"Month": "Oct",
"Amount": 752285
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 11,
"Month": "Nov",
"Amount": 324837
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "LC",
"MonthNum": 12,
"Month": "Dec",
"Amount": 788186
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 1,
"Month": "Jan",
"Amount": 658825
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 2,
"Month": "Feb",
"Amount": 114371
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 3,
"Month": "Mar",
"Amount": 5236
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 4,
"Month": "Apr",
"Amount": 207685
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 5,
"Month": "May",
"Amount": 235734
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 6,
"Month": "Jun",
"Amount": 139577
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 7,
"Month": "Jul",
"Amount": 228516
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 8,
"Month": "Aug",
"Amount": 360174
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 9,
"Month": "Sep",
"Amount": 374068
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 10,
"Month": "Oct",
"Amount": 62140
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 11,
"Month": "Nov",
"Amount": 740896
},
{
"CostCentre": "CC_1000",
"CostElement": "CE_6000",
"FiscalYear": "2022",
"Currency": "GC",
"MonthNum": 12,
"Month": "Dec",
"Amount": 946384
}
]
Code language: JSON / JSON with Comments (json)