Use to convert short, wide data tables into long, narrow data tables. Select columns are transposed, with the column names converted into values across multiple rows.
Perhaps the easiest example to understand is to think of a data table with months listed as column headers:
Melting this data table would convert all of the month columns into rows.
By specifying which columns to transpose and which columns to leave alone, this becomes a powerful tool. Making this conversion in other ETL tools could require a dozen more steps.
To establish the source and target, first select the data table to be exported from the Source Table dropdown menu. Next, select the target file path from PlaidCloud Document using the dropdown menu to select the appropriate account before navigating to the actual directory in the section immediately below. Finally, provide the target file with a descriptive name.
Providing a file extension is advised but not required by Analyze. The data table will be exported into the appropriate file format with or without an extension.
This section is a bit different from the standard Table Data Selection. Basically this is used to specify which columns are to be used in the Melt operation. This includes ID columns and Variable/Value columns.
The column layout in the Pre-Melt Table Data Selection does NOT reflect the column layout of the output data table. Target data table layout is specified in the Melt Layout section.
For more details regarding Table Data Selection, see details here: Table Data Selection
To allow for maximum flexibility, data filters are available on the source data and the target data. For larger data sets, it can be especially beneficial to filter out rows on the source so the remaining operations are performed on a smaller data set.
Any valid Python expression is acceptable to subset the data. Please see Expressions for more details and examples.
Compound filters must have individual elements wrapped in parentheses. For example, if filtering for Temperature and Humidity, a valid filter would look like this:
To report duplicates, select the Report Duplicates in Table checkbox and then specify an output table which will contain all of the duplicate records.
This will not remove the duplicate items from the target data table. To remove duplicate items, use the Distinct menu options as specified in the [Table Data Selection](../transforms/common_features#table-data-selection) section.
Any valid Python expression is acceptable to subset the data. Please see Expressions for more details and examples
Example code here
To limit the data, check the Apply Row Slicer box and then specify the following:
To limit the data, simply check the Apply Row Slicer box and then specify the following:
There is a Guess Layout button available to allow Analyze a first crack at specifying ID columns. By default, all text (data type of String) columns are placed in the Keys section. Numeric columns are not placed into Keys by default, but they are allowed to be there based on the model’s needs.
The target data table’s structure will consist of all ID Columns plus the names specified for Variable Column Name and Value Column Name.
ID columns are the columns which remain in tact. These columns are effectively repeated for every instance of a variable/value combination. For a monthly table, this would result in 12 repetitions of ID columns.
ID columns can be added automatically or manually. To add the columns automatically, use the aforementioned Guess Layout button. To add additional columns manually, right click anywhere in the section and select either Insert Row or Append Row, to add a row prior to the currently selected row or to add a row at the end, respectively. Then, type the column name to use as an ID.
To remove a field from the IDs, simply right-click and select Delete.
There are 2 values to specify. Both of these values will become column names in the target data table.
In this example, we will use the table listed in the Description section above.
First, the Source Table and Target Table are specified.
Next, use the Inspect Source and Populate Both Mapping Tables buttons to make all of the columns available for the melt operation. Keep in mind, this does not reflect the target data table’s layout. For example, if the report was only on the first half of the year, JUL-DEC months could be deleted from the right pane.
No Data Filters are used, so this tab is not shown.
Lastly, the Guess button is used to correctly identify that BudgetCategory is the only ID / Key value. Remember that this is a text field (not numeric), so that is why it’s identified as such. All of the other columns (all 12 months JAN – DEC) will be included in the melt operation. As such, Month is a good Variable Column Name to reflect what the columns in the source data table represented. Dollars will be used for Value Column Name as all of the data was in US dollar format.
For an additional example, please see the Transpose Computer Rankings from Columns to Rows section of the BCS Demo.