Wiki source code of 07c. ETL Package
Last modified by Ross Beck on 06/11/2025, 14:23
Show last authors
| author | version | line-number | content |
|---|---|---|---|
| 1 | {{box cssClass="floatinginfobox" title="**Contents**"}} | ||
| 2 | {{toc/}} | ||
| 3 | {{/box}} | ||
| 4 | |||
| 5 | The ETL Package Wizard encompasses a number of Data Sources to facilitate the creation of data structures in a single screen. | ||
| 6 | |||
| 7 | Rather than creating separate Indexes and processing them to create more Indexes, the ETL Wizard can be used to connect to and process data resulting in a single Data Source, Data Source Group and Index. | ||
| 8 | |||
| 9 | Navigate to the ETL Package Wizard screen by clicking **Wizards**, then **ETL Package**. | ||
| 10 | |||
| 11 | Click the **New** button to begin the process. | ||
| 12 | |||
| 13 | = Details = | ||
| 14 | |||
| 15 | Enter the name in the **Name** textbox. This name must be unique. | ||
| 16 | |||
| 17 | Use the **Tags** textbox to add associated search terms to the data source. This allows access via customised strings when using the search bar. | ||
| 18 | |||
| 19 | To automatically restrict access to the Index once built, enable the **Sensitive** option. Only users with explicit permission will be able to access the Index. | ||
| 20 | |||
| 21 | Use the **Notes** textbox to enter additional information for other administrator users to view when editing the structure. | ||
| 22 | |||
| 23 | The **Index Method** drop-down list specifies the build method to be used when building the Index. | ||
| 24 | |||
| 25 | The **Complete** option will result in all items in a data source being indexed when a refresh takes place. Once refreshed, a new version number is applied to the Index. Each run completely refreshes the Index. | ||
| 26 | |||
| 27 | Building an **Incremental** Index enables an optimised refresh process when rebuilding Indexes to account for any changes. Rather than rebuilding the entire Index, only data that has been modified or added will be processed. New and changed records are added as new rows. This maintains the history of a record that may have changed over time, and no de-duplication is applied. When selected, the **Incremental Identifier** drop-down list is revealed. This is required to identify changes, using the selected field to detect when new data is greater than the previous highest value. | ||
| 28 | |||
| 29 | The **Cumulative** option works the same as an **Incremental** build, but replaces duplicates as well as adding new data. Duplicate records are removed from the result set based on the **Increment Identifier** and **Primary Key**. | ||
| 30 | |||
| 31 | Use the **Index Size** drop-down list to specify the expected number of records the resulting Index will contain. This is an indicative value and does not need to be exact. | ||
| 32 | |||
| 33 | Finally, specify the the Search Engine the Index will be added to from the **Search Engines** drop-down list and the Collection from the **Collections** drop-down list. | ||
| 34 | |||
| 35 | = Stages = | ||
| 36 | |||
| 37 | To add a new stage to the Index, click the **+** icon. This will present a list of the available Data Sources. | ||
| 38 | |||
| 39 | Indexes built as stages in the ETL Wizard will not appear in any other screen, and are treated as 'temporary' Indexes that facilitate the final output from the wizard. | ||
| 40 | |||
| 41 | To create a copy of a stage, click the **Cog** icon and click **Copy**. To move a stage, click the **Cog** icon and click the required move option. Please note that stages that reference values from a previous stage will require changes to ensure the structure of the processing is conserved. | ||
| 42 | |||
| 43 | To delete a stage, click the **Cog** icon and click **Remove**. | ||
| 44 | |||
| 45 | == Amalgamated == | ||
| 46 | |||
| 47 | Using the Amalgamated Data Source, two or more existing Indexes can be joined based on a number options to de-duplicate and filter the resulting output. | ||
| 48 | |||
| 49 | For more information, please refer to the Amalgamated Data Source Wizard chapter. | ||
| 50 | |||
| 51 | == Calculations == | ||
| 52 | |||
| 53 | This stage is used to create additional fields using the Data Source Group calculations functionality. | ||
| 54 | |||
| 55 | Please note that any field required for a calculation must be included in a prior stage. | ||
| 56 | |||
| 57 | For more information regarding the available calculation functions, please refer to the [[Calculation Builder>>doc:Technical Documentation.Legacy Documentation.CXAIR 2020\.2.User Guide (2020\.2).02\. Reporting.2c\. Crosstabs.2ci\. Calculation Builder.WebHome]] chapter. | ||
| 58 | |||
| 59 | == Calendar Assistant == | ||
| 60 | |||
| 61 | The Calendar Assistant Data Source adds rows to the provided Index created from intersections in the specified date ranges. | ||
| 62 | |||
| 63 | Select a source value from the Index drop-down list to populate the Start Date and End Date drop-down lists with the required date fields. The Use Today If End Date Is Null option, when checked, will replace the End Date with the current system date if the End Date is NULL. | ||
| 64 | |||
| 65 | Enable the Build Daily Calendar option if an automatic daily refresh the data source is required, or the Months option to instead add values on a monthly basis. | ||
| 66 | |||
| 67 | == Data Mart == | ||
| 68 | |||
| 69 | The Data Mart Data Source allows users to extract a portion of an existing Index and incorporate the data into a standalone Data Source. | ||
| 70 | |||
| 71 | By saving a query within an Index that restricts the data at the required level, this query can then be selected to create an independent data source with a view to increasing performance and removing any irrelevant entries for the end user. | ||
| 72 | |||
| 73 | A Data Mart cannot be built from an unstructured Index. | ||
| 74 | |||
| 75 | Click the **Select Query** button to open the Load screen, where a previously saved Query can be selected, the results of which will drive the Index records. | ||
| 76 | |||
| 77 | == Database == | ||
| 78 | |||
| 79 | The Database Data Source contains the options to create Index structures from a number of database types along with advanced options such as procedural correction and derived dates. | ||
| 80 | |||
| 81 | For more information regarding the available options, please refer to the Database Data Source Wizard chapter. | ||
| 82 | |||
| 83 | == ETL == | ||
| 84 | |||
| 85 | By creating an Index that contains a number of techniques to loop through the data, flags can be returned that can be used to manipulate the underlying records. | ||
| 86 | |||
| 87 | This is achieved using the Extract, Transform and Load (ETL) Data Source to identify and amend target data through a number of configurable stages. | ||
| 88 | |||
| 89 | Select the required Index from the **Index** drop-down list to reveal the options below. | ||
| 90 | |||
| 91 | === Stage Management === | ||
| 92 | |||
| 93 | Check the **Disable Stage** textbox to retain the tab and all settings, but not apply it to the output. | ||
| 94 | |||
| 95 | === Mandatory === | ||
| 96 | |||
| 97 | Select fields from the **Keys** drop-down list that will be used to identify each unique record when looping through the data. Click the **X** icon next to a selected field remove it. | ||
| 98 | |||
| 99 | Select fields from the **Order** drop-down list that define the ordering of the data when the looping though the records. This also impacts the ordering in the [[Query>>doc:Technical Documentation.CXAIR.User Guide.02\. Reporting.2a\. Query.WebHome]] screen once the Index has been built. | ||
| 100 | |||
| 101 | === General === | ||
| 102 | |||
| 103 | Enable the **Squash** option to filter the data to a single record for the defined Key. When using **Squash**, only fields added to the drop-down lists in the **Copy** tab will appear in the next stage (or final ETL output if there are no subsequent stages). As such, the field(s) selected as the primary key from the **Keys** drop-down list will be automatically added to the **Distinct** list found in the **Copy** tab. This is to prevent zero fields being present in the output. Please note that a transpose stage cannot follow a squash, and this function will need to be performed in a separate Index. | ||
| 104 | |||
| 105 | Enable the **Others** option to copy all documents that do not matched a defined Saved Query and append them to the bottom of the Index. | ||
| 106 | |||
| 107 | The **Saved Query** option can be used to subset the data within an Index. This is applied before the looping techniques. | ||
| 108 | |||
| 109 | === Display Field Mapping === | ||
| 110 | |||
| 111 | Each tab in this section enables the user to perform a number of different tasks. Any tab that has had an option set will be highlighted with a star in the top right corner. | ||
| 112 | |||
| 113 | ==== Flags ==== | ||
| 114 | |||
| 115 | These functions will return a value of ‘True’ or ‘False’ against every specified field for each row in the Index. The data is not filtered, only a flag is set. | ||
| 116 | |||
| 117 | Select individual values using the relevant drop-down list, or click **All** to apply the flag to all fields. Delete individual selections by clicking the **X** icon next to the relevant field, or clear all selections by clicking **None**. | ||
| 118 | |||
| 119 | The following flags are available: | ||
| 120 | |||
| 121 | (% border="1" cellspacing="0" class="MsoTableGrid" style="border-collapse:collapse; border:1pt solid windowtext; width:1024px" %) | ||
| 122 | |(% style="background-color:#ed7d31; background:#ed7d31; border-style:solid; border-width:1pt; width:274px" width="279" %)((( | ||
| 123 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 124 | (% style="color:white" %)Function | ||
| 125 | )))|(% style="background-color:#ed7d31; background:#ed7d31; border-bottom-style:solid; border-bottom-width:1pt; border-left:1pt solid; border-right-style:solid; border-right-width:1pt; border-top-style:solid; border-top-width:1pt; width:749px" width="287" %)((( | ||
| 126 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 127 | (% style="color:white" %)Description | ||
| 128 | ))) | ||
| 129 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 130 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 131 | Has Null | ||
| 132 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 133 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 134 | Returns true against all the key if any values of the specified field(s) contain a NULL. | ||
| 135 | |||
| 136 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 137 | Based on the field(s) that have been set to use this option, a new column called HASNULL will be created. It will populate the column with a value of true for all values of the key is any row contains a NULL (for the key). | ||
| 138 | ))) | ||
| 139 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 140 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 141 | Is Minimum | ||
| 142 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 143 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 144 | (% style="color:#1a1c1e" %)Returns true for each minimum value of the specified field(s). | ||
| 145 | |||
| 146 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 147 | (% style="color:#1a1c1e" %)Based on the field(s) that have been set to use this option, a new column called ISMIN will be created. It will populate the column with a value of true for the minimum value(s) it finds in the specified field. If it finds duplicate minimum values, it will flag true against each one. | ||
| 148 | ))) | ||
| 149 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 150 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 151 | Is First minimum | ||
| 152 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 153 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 154 | (% style="color:#1a1c1e" %)Returns true for the first minimum value of the specified field(s). | ||
| 155 | |||
| 156 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 157 | (% style="color:#1a1c1e" %)Based on the field(s) that have been set to use this option, a new column called ISFIRSTMIN will be created. It will populate the column with a value of true for the first minimum value it finds in the specified field (based on the order of the data). | ||
| 158 | ))) | ||
| 159 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 160 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 161 | Is Last Minimum | ||
| 162 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 163 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 164 | (% style="color:#1a1c1e" %)Returns true for the last minimum value of the specified fields(s). | ||
| 165 | |||
| 166 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 167 | (% style="color:#1a1c1e" %)Based on the field(s) that have been set to use this option, a new column called ISLASTMIN will be created. It will populate the column with a value of true for the last minimum value it finds in the specified field (based on the order of the data). | ||
| 168 | ))) | ||
| 169 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 170 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 171 | Is Maximum | ||
| 172 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 173 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 174 | (% style="color:#1a1c1e" %)Returns true for each maximum value of the specified field(s). | ||
| 175 | |||
| 176 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 177 | (% style="color:#1a1c1e" %)Based on the field(s) that have been set to use this option, a new column called ISMAX will be created. It will populate the column with a value of true for the maximum value(s) it finds in the specified field. If it finds duplicate maximum values, it will flag true against each one. | ||
| 178 | ))) | ||
| 179 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 180 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 181 | Is First Maximum | ||
| 182 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 183 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 184 | (% style="color:#1a1c1e" %)Returns true for the first maximum value of the specified field(s). | ||
| 185 | |||
| 186 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 187 | (% style="color:#1a1c1e" %)Based on the field(s) that have been set to use this option, a new column called ISFIRSTMAX will be created. It will populate the column with a value of true for the first maximum value it finds in the specified field (based on the order of the data). | ||
| 188 | ))) | ||
| 189 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 190 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 191 | Is Last Maximum | ||
| 192 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 193 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 194 | (% style="color:#1a1c1e" %)Returns true for the last maximum value of the specified field(s). | ||
| 195 | |||
| 196 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 197 | (% style="color:#1a1c1e" %)Based on the field(s) that have been set to use this option, a new column called ISLASTMAX will be created. It will populate the column with a value of true for the last maximum value it finds in the specified field (based on the order of the data). | ||
| 198 | ))) | ||
| 199 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 200 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 201 | Is First | ||
| 202 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 203 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 204 | (% style="color:#1a1c1e" %)Returns true for the first value found in the specified field(s). | ||
| 205 | |||
| 206 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 207 | (% style="color:#1a1c1e" %)Based on the field(s) that have been set to use this option, a new column called ISFIRST will be created. It will populate the column with a value of true for the first value it finds in the specified field (based on the order of the data). | ||
| 208 | ))) | ||
| 209 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 210 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 211 | Is Last | ||
| 212 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 213 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 214 | (% style="color:#1a1c1e" %)Returns true for the last value found in the specified field(s). | ||
| 215 | |||
| 216 | |||
| 217 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 218 | (% style="color:#1a1c1e" %)Based on the field(s) that have been set to use this option, a new column called ISLAST will be created. It will populate the column with a value of true for the last value it finds in the specified field (based on the order of the data). | ||
| 219 | ))) | ||
| 220 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 221 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 222 | Is Greater Than Zero | ||
| 223 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 224 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 225 | Returns true for each value of the specified field(s) where the value of the field is greater than zero. | ||
| 226 | |||
| 227 | |||
| 228 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 229 | Based on the field(s) that have been set to use this option, a new column called ISGREATERTHANZERO will be created. It will populate the column with a value of true for each instance it finds where the specified field is greater than zero. | ||
| 230 | ))) | ||
| 231 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 232 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 233 | Is Greater Than One | ||
| 234 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 235 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 236 | Returns true for each value of the specified field(s) where the value of the field is greater than one. | ||
| 237 | |||
| 238 | |||
| 239 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 240 | Based on the field(s) that have been set to use this option, a new column called ISGREATERTHANONE will be created. It will populate the column with a value of true for each instance it finds where the specified field is greater than one. | ||
| 241 | ))) | ||
| 242 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:274px" width="279" %)((( | ||
| 243 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 244 | Is Working Day | ||
| 245 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:749px" width="287" %)((( | ||
| 246 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 247 | Returns true for each value of the specified field(s) where the date value falls on a working day, as specified in the [[System Settings>>doc:Technical Documentation.CXAIR.Administration Guide.Status Monitoring.System Settings.WebHome||anchor="Working Days"]]. | ||
| 248 | |||
| 249 | |||
| 250 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 251 | Based on the field(s) that have been set to use this option, a new column called ISWORKINGDAY will be created. It will populate the column with a value of true for each instance it finds a date matching the specified working days. | ||
| 252 | ))) | ||
| 253 | |||
| 254 | ==== Copy ==== | ||
| 255 | |||
| 256 | |||
| 257 | (% border="1" cellspacing="0" class="MsoTableGrid" style="border-collapse:collapse; border:1pt solid windowtext; width:1024px" %) | ||
| 258 | |(% style="background-color:#ed7d31; background:#ed7d31; border-style:solid; border-width:1pt; width:273px" width="282" %)((( | ||
| 259 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 260 | (% style="color:white" %)Function | ||
| 261 | )))|(% style="background-color:#ed7d31; background:#ed7d31; border-bottom-style:solid; border-bottom-width:1pt; border-left:1pt solid; border-right-style:solid; border-right-width:1pt; border-top-style:solid; border-top-width:1pt; width:750px" width="283" %)((( | ||
| 262 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 263 | (% style="color:white" %)Description | ||
| 264 | ))) | ||
| 265 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:273px" width="282" %)((( | ||
| 266 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 267 | Copy | ||
| 268 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:750px" width="283" %)((( | ||
| 269 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 270 | Copies all values of the specified field(s) into a single record. | ||
| 271 | |||
| 272 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 273 | Based on the field(s) that have been set to use this option, a new column called COPY will be created. It will populate the column with all value(s) it finds in the specified field against every row for that specified key. | ||
| 274 | ))) | ||
| 275 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:273px" width="282" %)((( | ||
| 276 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 277 | Analyzed | ||
| 278 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:750px" width="283" %)((( | ||
| 279 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 280 | (% style="color:#1a1c1e" %)Copies all values of the specified field(s), ordered by the Order value. | ||
| 281 | |||
| 282 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 283 | (% style="color:#1a1c1e" %)Based on the field(s) that have been set to use this option, a new column called ANALYZED will be created. It will populate the column with all value(s) it finds in the specified field against every row for that specified key. The order of the values will be set based on the Order value. The fields will be analyzed to enable free text searching | ||
| 284 | ))) | ||
| 285 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:273px" width="282" %)((( | ||
| 286 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 287 | Unique | ||
| 288 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:750px" width="283" %)((( | ||
| 289 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 290 | (% style="color:#1a1c1e" %)Copies all unique values of the specified field(s), ordered ascendingly, into a single record. | ||
| 291 | |||
| 292 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 293 | (% style="color:#1a1c1e" %)Based on the field(s) that have been set to use this option, a new column called UNIQUE will be created. It will populate the column with all unique value(s) it finds in the specified field against every row for that specified key. The order of the values will be in ascending order. | ||
| 294 | ))) | ||
| 295 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:273px" width="282" %)((( | ||
| 296 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 297 | Transpose | ||
| 298 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:750px" width="283" %)((( | ||
| 299 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 300 | Turns every unique value in a column into its own field. | ||
| 301 | |||
| 302 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 303 | Based on the field that has been set to use this option, new columns will be created. It will populate the columns with all the corresponding values from the field specified by "Transpose Values". | ||
| 304 | ))) | ||
| 305 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:273px" width="282" %)((( | ||
| 306 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 307 | Flattened Transpose | ||
| 308 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:750px" width="283" %)((( | ||
| 309 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 310 | For example if a value 'A' in Flattened Transpose Headers corresponds with the values "B" and "C" in Flattened Transpose Values then two columns will be created "A B" and "A C". | ||
| 311 | |||
| 312 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 313 | The fields in the created columns are populated with a "Y" or left blank. | ||
| 314 | |||
| 315 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 316 | Based on the field(s) that have been set to use this option, a new column called DISTINCT will be created. It will populate the column with all distinct value(s) it finds in the specified field against every row for that specified key. The order of the values will be set based on the Order value. | ||
| 317 | ))) | ||
| 318 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:273px" width="282" %)((( | ||
| 319 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 320 | Distinct | ||
| 321 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:750px" width="283" %)((( | ||
| 322 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 323 | Copies all distinct values of the specified field(s), ordered by the Order value. | ||
| 324 | |||
| 325 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 326 | Based on the field(s) that have been set to use this option, a new column called DISTINCT will be created. It will populate the column with all distinct value(s) it finds in the specified field against every row for that specified key. The order of the values will be set based on the Order value. | ||
| 327 | ))) | ||
| 328 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:273px" width="282" %)((( | ||
| 329 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 330 | Concatenate | ||
| 331 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:750px" width="283" %)((( | ||
| 332 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 333 | Concatenates all values (comma separated) from the selected field based on the specified key(s). | ||
| 334 | |||
| 335 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 336 | Based on the field(s) that have been set to use this option, a new column called CONCATENATE will be created. It will populate the column with a concatenated, comma separated string of all values found in the selected field based on the specified key. It displays all values, not all unique values. | ||
| 337 | ))) | ||
| 338 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:273px" width="282" %)((( | ||
| 339 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 340 | Concatenate Literal | ||
| 341 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:750px" width="283" %)((( | ||
| 342 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 343 | Concatenates all values (no spaces) from the selected field based on the specified key(s). | ||
| 344 | |||
| 345 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 346 | Based on the field(s) that have been set to use this option, a new column called CONCATENATE will be created. It will populate the column with a concatenated string of all values found in the selected field based on the specified key. It displays all values, not all unique values. | ||
| 347 | ))) | ||
| 348 | |||
| 349 | ==== Positional ==== | ||
| 350 | |||
| 351 | |||
| 352 | (% border="1" cellspacing="0" class="MsoTableGrid" style="border-collapse:collapse; border:1pt solid windowtext; width:1024px" %) | ||
| 353 | |(% style="background-color:#ed7d31; background:#ed7d31; border-style:solid; border-width:1pt; width:271px" width="282" %)((( | ||
| 354 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 355 | (% style="color:white" %)Function | ||
| 356 | )))|(% style="background-color:#ed7d31; background:#ed7d31; border-bottom-style:solid; border-bottom-width:1pt; border-left:1pt solid; border-right-style:solid; border-right-width:1pt; border-top-style:solid; border-top-width:1pt; width:752px" width="284" %)((( | ||
| 357 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 358 | (% style="color:white" %)Description | ||
| 359 | ))) | ||
| 360 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 361 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 362 | First | ||
| 363 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 364 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 365 | Writes the maximum value of the specified field(s) based on the selected key. | ||
| 366 | |||
| 367 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 368 | Based on the field(s) that have been set to use this option, a new column called MAX will be created. It will populate the column with the maximum value it finds in the specified field against every row for that specified key. | ||
| 369 | ))) | ||
| 370 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 371 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 372 | Last | ||
| 373 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 374 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 375 | Writes the last value of the specified field(s) based on the selected key and the order of the data. | ||
| 376 | |||
| 377 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 378 | Based on the field(s) that have been set to use this option, a new column called LAST will be created. It will populate the column with the last value it finds in the specified field against every row for that specified key (based on the order that has been set). | ||
| 379 | ))) | ||
| 380 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 381 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 382 | Previous | ||
| 383 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 384 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 385 | Writes the previous value of the specified field(s) based on the selected key and the order of the data. | ||
| 386 | |||
| 387 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 388 | Based on the field(s) that have been set to use this option, a new column called PREVIOUS will be created. It will populate the column with the previous value it finds in the specified field against the specified key (based on the order that has been set). | ||
| 389 | ))) | ||
| 390 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 391 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 392 | Next | ||
| 393 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 394 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 395 | Writes the next value of the specified field(s) based on the selected key and the order of the data. | ||
| 396 | |||
| 397 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 398 | Based on the field(s) that have been set to use this option, a new column called NEXT will be created. It will populate the column with the next value it finds in the specified field against the specified key (based on the order that has been set). | ||
| 399 | ))) | ||
| 400 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 401 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 402 | Lower | ||
| 403 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 404 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 405 | Writes the lower value of the specified field(s) based on the selected key and the order of the data. | ||
| 406 | |||
| 407 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 408 | Based on the field(s) that have been set to use this option, a new column called LOWER will be created. It will populate the column with the next value it finds in the specified field against the specified key (based on the order that has been set). | ||
| 409 | ))) | ||
| 410 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 411 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 412 | Higher | ||
| 413 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 414 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 415 | Provides an incremental number against several rows of data based on the specified key(s) and the data containing dates that overlap. | ||
| 416 | |||
| 417 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 418 | Based on the field(s) that have been set to use this option, a new column called OVERLAP will be created. It will populate the column with a numeric value starting from 0 and incrementing by 1 for each subsequent group of records that have dates that overlap. | ||
| 419 | ))) | ||
| 420 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 421 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 422 | Overlapping Dates Sequence | ||
| 423 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 424 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 425 | Writes the higher value of the specified field(s) based on the selected key and the order of the data. | ||
| 426 | |||
| 427 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 428 | Based on the field(s) that have been set to use this option, a new column called HIGHER will be created. It will populate the column with the next value it finds in the specified field against the specified key (based on the order that has been set). | ||
| 429 | ))) | ||
| 430 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 431 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 432 | Sequence Starting from 0 | ||
| 433 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 434 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 435 | Provides an incremental number against each row of data based on the specified key(s) and the order of the data | ||
| 436 | |||
| 437 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 438 | Based on the field(s) that have been set to use this option, a new column called SEQUENCE0 will be created. It will populate the column with a numeric value starting from 0 and incrementing by 1 for each subsequent record for the specified key. | ||
| 439 | ))) | ||
| 440 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 441 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 442 | Sequence Starting From 1 On Value Change | ||
| 443 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 444 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 445 | Provides an incremental number against each row of data based on the specified key(s) and the order of the data. | ||
| 446 | |||
| 447 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 448 | Based on the field(s) that have been set to use this option, a new column called SEQUENCEVALUECHANGE will be created. It will populate the column with a numeric value starting from 1 and incrementing by 1 for each subsequent record where the value changes for the specified key. | ||
| 449 | ))) | ||
| 450 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 451 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 452 | Sequence Starting from 1 | ||
| 453 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 454 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 455 | Provides an incremental number against each row of data based on the specified key(s) and the order of the data. | ||
| 456 | |||
| 457 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 458 | Based on the field(s) that have been set to use this option, a new column called SEQUENCE1 will be created. It will populate the column with a numeric value starting from 1 and incrementing by 1 for each subsequent record for the specified key. | ||
| 459 | ))) | ||
| 460 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 461 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 462 | Descending Sequence Ending With 0 | ||
| 463 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 464 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 465 | Provides a descending number against each row of data based on the specified key(s) and the order of the data. | ||
| 466 | |||
| 467 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 468 | Based on the field(s) that have been set to use this option, a new column called REVERSESEQUENCE0 will be created. It will populate the column with a numeric value starting from the number of records minus one and decrementing by 1 for each subsequent record for the specified key. | ||
| 469 | ))) | ||
| 470 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 471 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 472 | Descending Sequence Ending With 1 | ||
| 473 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 474 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 475 | Provides a descending number against each row of data based on the specified key(s) and the order of the data. | ||
| 476 | |||
| 477 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 478 | Based on the field(s) that have been set to use this option, a new column called REVERSESEQUENCE1 will be created. It will populate the column with a numeric value starting from the number of records and decrementing by 1 for each subsequent record for the specified key. | ||
| 479 | ))) | ||
| 480 | |||
| 481 | ==== Aggregations ==== | ||
| 482 | |||
| 483 | |||
| 484 | (% border="1" cellspacing="0" class="MsoTableGrid" style="border-collapse:collapse; border:1pt solid windowtext; width:1024px" %) | ||
| 485 | |(% style="background-color:#ed7d31; background:#ed7d31; border-style:solid; border-width:1pt; width:272px" width="281" %)((( | ||
| 486 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 487 | (% style="color:white" %)Function | ||
| 488 | )))|(% style="background-color:#ed7d31; background:#ed7d31; border-bottom-style:solid; border-bottom-width:1pt; border-left:1pt solid; border-right-style:solid; border-right-width:1pt; border-top-style:solid; border-top-width:1pt; width:751px" width="284" %)((( | ||
| 489 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 490 | (% style="color:white" %)Description | ||
| 491 | ))) | ||
| 492 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:272px" width="281" %)((( | ||
| 493 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 494 | Minimum | ||
| 495 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:751px" width="284" %)((( | ||
| 496 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 497 | Writes the minimum value of the specified field(s) based on the selected key. | ||
| 498 | |||
| 499 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 500 | Based on the field(s) that have been set to use this option, a new column called MIN will be created. It will populate the column with the minimum value it finds in the specified field against every row for that specified key. | ||
| 501 | ))) | ||
| 502 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:272px" width="281" %)((( | ||
| 503 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 504 | Maximum | ||
| 505 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:751px" width="284" %)((( | ||
| 506 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 507 | Writes the maximum value of the specified field(s) based on the selected key. | ||
| 508 | |||
| 509 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 510 | Based on the field(s) that have been set to use this option, a new column called MAX will be created. It will populate the column with the maximum value it finds in the specified field against every row for that specified key. | ||
| 511 | ))) | ||
| 512 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:272px" width="281" %)((( | ||
| 513 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 514 | Count | ||
| 515 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:751px" width="284" %)((( | ||
| 516 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 517 | Provides a count of all non-NULL values in a specified field for a selected key. | ||
| 518 | |||
| 519 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 520 | Based on the field(s) that have been set to use this option, a new column called COUNT will be created. It will populate the column with a count of all non-NULL values it finds in the specified field based on the specified key. | ||
| 521 | ))) | ||
| 522 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:272px" width="281" %)((( | ||
| 523 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 524 | Count All | ||
| 525 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:751px" width="284" %)((( | ||
| 526 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 527 | Provides a count of all (including NULL) values in a specified field for a selected key. | ||
| 528 | |||
| 529 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 530 | Based on the field(s) that have been set to use this option, a new column called COUNT will be created. It will populate the column with a count of all (including NULL) values it finds in the specified field based on the specified key. | ||
| 531 | ))) | ||
| 532 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:272px" width="281" %)((( | ||
| 533 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 534 | Count Unique | ||
| 535 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:751px" width="284" %)((( | ||
| 536 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 537 | Provides a count of unique values in a specified field for a selected key. | ||
| 538 | |||
| 539 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 540 | Based on the field(s) that have been set to use this option, a new column called COUNTUNIQUE will be created. It will populate the column with a count of unique values it finds in the specified field based on the specified key. | ||
| 541 | ))) | ||
| 542 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:272px" width="281" %)((( | ||
| 543 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 544 | Sum | ||
| 545 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:751px" width="284" %)((( | ||
| 546 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 547 | Aggregates the data based on the specified key(s). | ||
| 548 | |||
| 549 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 550 | Based on the field(s) that have been set to use this option, a new column called SUM will be created. It will populate the column with a sum of the field based on the specified key. | ||
| 551 | ))) | ||
| 552 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:272px" width="281" %)((( | ||
| 553 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 554 | Cumulative Sum | ||
| 555 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:751px" width="284" %)((( | ||
| 556 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 557 | Aggregates the data based on the specified key(s). | ||
| 558 | |||
| 559 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 560 | Based on the field(s) that have been set to use this option, a new column called CUMULATIVESUM will be created. It will populate the column with a cumulative sum of the field based on the specified key. | ||
| 561 | ))) | ||
| 562 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:272px" width="281" %)((( | ||
| 563 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 564 | Average | ||
| 565 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:751px" width="284" %)((( | ||
| 566 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 567 | Averages the data based on the specified key(s). | ||
| 568 | |||
| 569 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 570 | Based on the field(s) that have been set to use this option, a new column called AVERAGE will be created. It will populate the column with an average of the field based on the specified key. | ||
| 571 | ))) | ||
| 572 | |||
| 573 | ==== Banding and Ranking ==== | ||
| 574 | |||
| 575 | |||
| 576 | (% border="1" cellspacing="0" class="MsoTableGrid" style="border-collapse:collapse; border:1pt solid windowtext; width:1024px" %) | ||
| 577 | |(% style="background-color:#ed7d31; background:#ed7d31; border-style:solid; border-width:1pt; width:271px" width="283" %)((( | ||
| 578 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 579 | (% style="color:white" %)Function | ||
| 580 | )))|(% style="background-color:#ed7d31; background:#ed7d31; border-bottom-style:solid; border-bottom-width:1pt; border-left:1pt solid; border-right-style:solid; border-right-width:1pt; border-top-style:solid; border-top-width:1pt; width:752px" width="283" %)((( | ||
| 581 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 582 | (% style="color:white" %)Description | ||
| 583 | ))) | ||
| 584 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="283" %)((( | ||
| 585 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 586 | Band | ||
| 587 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="283" %)((( | ||
| 588 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 589 | Creates bands for the data by diving the range of the numbers by 10. It is advised to use this function only when there are more than 10 unique values. | ||
| 590 | |||
| 591 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 592 | e.g if there are 100 entries, the bands would be 1-10, 11-20, 21-30 etc. | ||
| 593 | ))) | ||
| 594 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="283" %)((( | ||
| 595 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 596 | Ranking | ||
| 597 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="283" %)((( | ||
| 598 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 599 | Ranks the values from 1 the highest to n the lowest. | ||
| 600 | ))) | ||
| 601 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="283" %)((( | ||
| 602 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 603 | Rank Within | ||
| 604 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="283" %)((( | ||
| 605 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 606 | Ranks the values from 1 the highest to n the lowest within the current set of records. | ||
| 607 | ))) | ||
| 608 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="283" %)((( | ||
| 609 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 610 | Range Band | ||
| 611 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="283" %)((( | ||
| 612 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 613 | Bands the values in 10 groups, the banding is calculated by adding 2 standard deviations to the mean value, subtracting the 2 standard deviations from the mean value, and then dividing this range into 10. | ||
| 614 | ))) | ||
| 615 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="283" %)((( | ||
| 616 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 617 | Percentile Frequency | ||
| 618 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="283" %)((( | ||
| 619 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 620 | Bands the values in 10 Percentile groups depending on the frequency of the values, ideally each group will have 10 percent of the values. | ||
| 621 | ))) | ||
| 622 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="283" %)((( | ||
| 623 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 624 | Percentile Value | ||
| 625 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="283" %)((( | ||
| 626 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 627 | Bands the values in 10 Percentile groups depending on the sum of the values, ideally each group will have 10 percent of the values. | ||
| 628 | ))) | ||
| 629 | |||
| 630 | ==== Calculations ==== | ||
| 631 | |||
| 632 | Using the Calculations tab, custom calculations can be added. | ||
| 633 | |||
| 634 | Give the calculation a name in the **Label** textbox, and click the **...** icon next to the Calculation box to open the [[Calculation Builder>>doc:Technical Documentation.CXAIR.User Guide.02\. Reporting.2c\. Crosstabs.2ci\. Calculation Builder.WebHome]]. Enter the required calculation and click **Apply** to save the changes made, or **Cancel** to discard changes. Please refer to the [[Calculation Builder>>doc:Technical Documentation.CXAIR.User Guide.02\. Reporting.2c\. Crosstabs.2ci\. Calculation Builder.WebHome]] chapter for more detailed information regarding the functionality and available options. | ||
| 635 | |||
| 636 | To add additional calculations, click **Add**. To remove an added calculation, click the **X** icon next to the relevant entry. | ||
| 637 | |||
| 638 | Please note that any field required for a calculation following a Squash stage must be selected in either the **Copy**, **Distinct** or **Unique** lists as part of that stage. | ||
| 639 | |||
| 640 | ==== Additional ==== | ||
| 641 | |||
| 642 | Additional Display Field Mapping options will become available if a value has been set for the **Is First Minimum**, **Is Last Minimum**, **Is First Maximum** or **Is Last Maximum** flags. | ||
| 643 | |||
| 644 | (% border="1" cellspacing="0" class="MsoTableGrid" style="border-collapse:collapse; border:1pt solid windowtext; width:1024px" %) | ||
| 645 | |(% style="background-color:#ed7d31; background:#ed7d31; border-style:solid; border-width:1pt; width:271px" width="282" %)((( | ||
| 646 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 647 | (% style="color:white" %)Function | ||
| 648 | )))|(% style="background-color:#ed7d31; background:#ed7d31; border-bottom-style:solid; border-bottom-width:1pt; border-left:1pt solid; border-right-style:solid; border-right-width:1pt; border-top-style:solid; border-top-width:1pt; width:752px" width="284" %)((( | ||
| 649 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 650 | (% style="color:white" %)Description | ||
| 651 | ))) | ||
| 652 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 653 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 654 | <Flag Name> <Field Name> | ||
| 655 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 656 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 657 | Based on the flag chosen, this outputs the value(s) in the field(s) selected as they appear where the flag is true, against each row for that key. | ||
| 658 | ))) | ||
| 659 | |(% style="border-style:none solid solid; border-top-color:initial; border-width:1pt; width:271px" width="282" %)((( | ||
| 660 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 661 | <Flag Name> <Field Name> Offset | ||
| 662 | )))|(% style="border-bottom:1pt solid windowtext; border-left:none; border-right:1pt solid windowtext; border-top:none; width:752px" width="284" %)((( | ||
| 663 | (% style="padding:0cm 5.4pt 0cm 5.4pt" %) | ||
| 664 | Allows an offset value to be returned. For example, entering **1** will return the next value in the list, and entering **-1** will return the previous value in the list. | ||
| 665 | ))) | ||
| 666 | |||
| 667 | == Reverse Transpose == | ||
| 668 | |||
| 669 | Use the Reverse Transpose Data Source to transpose values within rows into columns. | ||
| 670 | |||
| 671 | Use the **Primary Key** dropdown list to specify what will be used to identify the columns to be transposed. | ||
| 672 | |||
| 673 | Enter a row name in the **Transpose Field Name** textbox, and a name for the associated values in the **Transpose Value Name** textbox. | ||
| 674 | |||
| 675 | By default, blank fields are not included as part of the reverse transpose process. Enable the **Include Blank Fields** option to include them in the output. | ||
| 676 | |||
| 677 | == Rules == | ||
| 678 | |||
| 679 | A Rules data source will use pre-set rules to validate or update data. | ||
| 680 | |||
| 681 | Rules can be applied to individual fields and the output will inform the user of the number of rules that have passed or failed. Rules are categorised as either major or minor to allow the output to be split based on priority. | ||
| 682 | |||
| 683 | Please refer to the [[Rules>>doc:Technical Documentation.CXAIR.Administration Guide.Data Warehouse.a\. Rules.WebHome||anchor="Rules"]] section of the [[Data Warehouse>>doc:Technical Documentation.CXAIR.Administration Guide.Data Warehouse.WebHome]] for chapter more information. | ||
| 684 | |||
| 685 | == Store in Index == | ||
| 686 | |||
| 687 | Use this stage to specify a subset of data that will be used to create the resulting Index. This can be used at multiple times within a structure to create separate Indexes at a certain point in the order. | ||
| 688 | |||
| 689 | Specify the name of this subset Index using the **Create as Name** textbox, and specify the fields that will be included using the **Display Fields** drop-down list. | ||
| 690 | |||
| 691 | Finally, specify the maximum row count that the subset Index can contain using the **Number to Index** option. | ||
| 692 | |||
| 693 | == Transpose == | ||
| 694 | |||
| 695 | Use the Transpose Data Source to transpose values within columns into rows. | ||
| 696 | |||
| 697 | Use the **Primary Key** dropdown list to specify what will be used to identify the rows to be transposed. | ||
| 698 | |||
| 699 | Specify the fields in the **Field 1 (Column Headers)** dropdown list that will be transposed into column headers once the Index has been created. The fields specified in the **Field 2 (Values)** dropdown list will populate these new columns based on the primary key value. To include additional fields in the new Index, select them from the **Extra Fields (Distincts)** dropdown list. | ||
| 700 | |||
| 701 | If the fields specified in the **Field 2 (Values)** dropdown list contain multiple values, enabling the **Flatten** option will split these values into separate columns. | ||
| 702 | |||
| 703 | = Configure Index = | ||
| 704 | |||
| 705 | Once one or more stages are added, the **Configure Index** tab will be revealed. These options control the Index-level output that can be configured prior to completing the wizard. | ||
| 706 | |||
| 707 | For more information regarding the available options, please refer to the [[Creating an Index>>doc:Technical Documentation.CXAIR.Administration Guide.4\. Manual Index Creation.c\. Creating an Index.WebHome]] chapter. |