A transpose operation within a derived dataset allows you to put data that spans multiple columns into data in multiple rows. This is sometimes also referred to as a pivot on a dataset. For example, suppose you have a dataset that has a unique field such as StoreID, and has a number of fields containing zip codes. You can think of this perhaps as a set of zip codes that define a footprint for a store. The dataset is currently structured as in the screenshot below, with one unique row per store, and zip code fields called Zip1, Zip2, Zip3, etc.
Our objective is to transpose the dataset so that each Store/Zip combo has a unique row in the result dataset. If the original dataset had 20 stores (20 rows) and 75 Zip variables (Zip1 through Zip75), the resulting dataset would have 20 x 75 = 1500 rows, but now only three variables: StoreID, Zip, and Zip Number.
In LityxIQ, first begin editing the derived dataset using instructions here: https://support.lityxiq.com/241959-EditingDefining-a-Dataset-View.
Then open the Transpose panel. It will be empty if you have not yet defined a transpose operation. Otherwise, it will provide some information about how it has been defined. Use the Delete button to delete an existing transpose definition, and click the Edit button to create or edit the definition.
Editing a transpose opens a dialog with options that are explained below. The settings in the screenshot below relate to the example explained above.
Record Id Variables - This will show a list of all variable currently in the dataset to this point in the dataset processing. Select the variables (it can be one or many of them) whose values will be maintained from a single row in the existing dataset structure, and be expanded across multiple rows in the eventual transposed dataset structure. In this example, StoreID is the one variable that will be maintained in this way.
New Variable Name - Enter the name for the new variable that will be created using components from the names of the variables to be transposed. This will be a new variable in the transposed dataset. Its value in the resulting dataset in this example will be the integers 1 through 75 since these are the other components of the Zip1 through Zip75 variable names in the original dataset.
Enter Prefix? - Set this checkbox if you will enter a list of prefixes to identify the variables to be transposed. Leave unchecked to list suffixes. It relates to the value entered in the next option. In this example, we check the box because the variable names to be transposed are identified as those "starting with" ("prefixed by") the string "Zip".
Prefixes/Suffixes - Enter the prefixes (or suffixes if the checkbox above is left unchecked) of the variable names to be transposed. If there are multiple, separate them with a comma. The system will transpose all variables with the listed prefixes/suffixes. Do not include the separating character (see next option) in your prefix/suffix list. In this example, the only variable set being transposed are those starting with the string "Zip".
Separating Characters - Enter characters that appear in the variable names being transposed that separate the two components of the name. For example, if your variable names are Age_45-54 and Age_55-64, the underscore character (_) separates the components of the variable names. Leave this blank if there is no separating character. In this example, it is left empty for that reason (that is, the variable names Zip1, Zip2, etc have no character between the "Zip" string and the identifying integer).
After clicking Save, the Transpose panel in the View would show details as in the following:
When executing this sample derived dataset, and having no other operations after the transpose, the result will look something like the following (showing just a small number of the resulting rows).
After the transpose operation finishes, execution of the dataset processing continues to other stages (New Fields, Filter, etc) if they are defined.