How-To

Home > Data Manager > How-To

Import a SAS, SPSS, or Stata Dataset File

Users of LityxIQ may have datasets that were originally created or stored by an outside statistical processing system, such as SAS, SPSS, or Stata, and may want to use this data directly in LityxIQ without having to use an outside tool to covert it to an appropriate format. LityxIQ allows you to import files in these formats directly into a LityxIQ dataset. The file types supported are: - SAS datasets (.sas7dat files) - SAS Xport Files (.xpt files) - SPSS datasets (.sav files) - Stata...

Previewing a Raw Dataset Source

The Preview area in a raw dataset source definition allows you to take a look at the underlying dataset before importing it. To generate a data preview, first setup the all of the settings correctly, then click the Preview Data Source button. This process will retrieve the first 100 rows of data and display them in the preview window. You can scroll up/down and right/left to see more data. When Excel or Google Sheets is the selected file type, the View Worksheet Names button becomes availab...

Reviewing Automated Insights

Automated Insights Dialog Basics WATCH A TUTORIAL: https://lityx.com/automated-insights-tutorial/ When Automated Insights for a dataset are ready to review, you will find the results in the Automated Insights dialog. This can be opened using either of the first two methods mentioned in https://support.lityxiq.com/074862-Setting-up-Automated-Insights. When you open the dialog, if the insights are not ready yet, you will see a message explaining the reason (for example, they may still be in pr...

Executing Automated Insights

WATCH A TUTORIAL: https://lityx.com/automated-insights-tutorial/ If you have setup Automated Insights for a dataset, they will automatically be executed when the dataset is refreshed. There is nothing else to do. You do have the option of manually executing the insights, which is helpful in the situation where the dataset has been already recently refreshed, but you hadn't yet setup Automated Insights at the time of the refresh. In that case, from the Automated Insights dialog (see https://supp...

Setting up Automated Insights

Opening the Automated Insights Settings Dialog WATCH A TUTORIAL: https://lityx.com/automated-insights-tutorial/ There are multiple places from which you can initiate setting up a dataset for Automated Insights. 1) From the Selected Dataset menu and then clicking the button. 2) From the Process Flow by clicking the icon. and then clicking the button. 3) From within the dataset settings dialogs. For Raw Datasets, this is found in the Advanced section: and for Derived Datasets, ...

What are Automated Insights?

Automated Insights are a novel and easy to use feature in LityxIQ that uses machine learning algorithms to automatically determine the individual and multi-variate drivers of any variable in a dataset. Any dataset can easily be set to create automated insights, and new insights will be generated each time the dataset is refreshed. As a user, you only have to tell LityxIQ which variables you wish to generate the insights, and optionally which variables to exclude from the analysis. LityxIQ takes ...

Export Data to Snowflake

LityxIQ supports the ability to directly export a LityxIQ dataset into your Snowflake account. This can be used, for example, as a way to create Tableau dashboards that automatically refresh upon fresh data being created in LityxIQ. All dataset exports in LityxIQ start by setting its Export Settings. For general information on Export Settings, see https://support.lityxiq.com/002683-Export-a-Dataset---Settings. If you select a Snowflake connection as the Export Location, there are different set...

Import Data from Snowflake

Snowflake is a cloud-based highly scalable database (see http://www.snowflake.com for more information). Snowflake acts muhc like a standard SQL database with respect to how one interacts with it, and therefore importing Snowflake data is much like importing data from other SQL databases in LityxIQ. See https://support.lityxiq.com/096794-Import-Data-from-a-SQL-Database-Table for more information. Note that when setting the connection settings for Snowflake connections, you are required to enter...

View and Edit a Dataset Dictionary

A dataset's dictionary in LityxIQ is a list of all the variables (fields) in the dataset, their names, and for each variable, the type of data it contains. For datasets created from raw data sources, the dictionary is most often created automatically by LityxIQ by analyzing the data source and automatically determining the variables and data types. However, you always have the ability to change the dataset dictionary. This includes changing variable names and data types. For Derived Datasets,...

Defining QC (Quality Control) Rules for a Derived Dataset

QC, or Quality Control, rules provide a way to do error checking on a dataset after it finishes executing. When no QC rules are set, the dataset execution is considered a success when it completes (of course, other errors may have happened along the way, such as an invalid field definition). If QC rules are defined, each rule is checked when the dataset is otherwise finished executing. If any of the QC rules are evaluated to be be valid, the result of the execution is considered an "error" inste...

Defining a Transpose Operation in a Derived Dataset

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, ...

Batch Import Files

LityxIQ has the ability to auto-load external data in batch. For example, it can be setup so that any file in a particular folder on an FTP site will be loaded once they are placed there, and continue to do so in an automated, ongoing manner. This document will describe the settings specific to setting up a batch loading process. Other settings related to the settings for a Raw Dataset are found here: https://support.lityxiq.com/261835-Define-the-Source-Settings-for-a-Raw-Dataset. This will help...

Import Data from an Excel File or Google Sheets

An Excel file is a common format for holding data in rows and columns. The data is held in a spreadsheet, often within a workbook of multiple spreadsheets. Typically, this spreadsheet format is only used for relatively small datasets. This document will explain the options available for importing data from Excel sheets into LityxIQ. Note that this same approach and discussion holds for Google Sheets data as well. A spreadsheet is a file, so it can be stored and retrieved from a variety of types...

Import Data from a SQL Database Table

A SQL database table is a structured dataset with rows and columns. Many vendors provide SQL-style database compatibility, including SQL Server, Oracle, PostgreSQL, and Amazon Redshift. Data contained in these databases can be wide-ranging, including transactional data, prospect or CRM data, or Big Data. This document will explain how to import data from a SQL database table into LityxIQ. In order to access an external database, you first need to setup a Data Connection in LityxIQ. See https://...

Import a Delimited Text File

A delimited text file, sometimes called a "csv" file or a flat file, is a very common method for encapsulating a dataset. Almost all systems or other file formats have a way to export data into delimited files, making it an easy and simple way to exchange files between systems. However, despite there being fairly strict standards in place for how a delimited file is to be created, there are differences from one system to the next that require options to be set and sometimes cause issues. This do...

Create Multiple New Fields at Once

The new fields area of a derived dataset (defined in detail here https://support.lityxiq.com/608058-Defining-New-Fields-for-a-View) has the option to create multiple new fields at once. This functionality is a powerful way to apply the same basic operation to many variables at the same time. Two use case examples are show below. Example 1 - Take Absolute Value of Many Fields Suppose you have many numeric variables, and you want to create a new field related to each of them that contains the...

Creating Datasets - Advanced Tab

The Advanced tab contains options that are similar both when importing raw data (in the Define Dataset Source dialog), or when executing a derived dataset (in the Finalize and QC dialog). The options are described here. Sort and Join Keys - Select the field(s) that are most likely to be used in joins or aggregations in future operations with this dataset. The most common of these can be dragged to the top of the list. Making these selections will help with performance of data operations. Fil...

Configuration Dataset Settings Options

Configuration Variables (described in more detail here https://support.lityxiq.com/084396-Create-and-Utilize-a-Configuration-Variable), can be created dynamically as the result of either a data import process, or executing a derived dataset. In either case, the same interface options are available, and these are described here. The screenshot below is from the Finalization dialog when editing a derived dataset, but the same options are available in the Define Dataset Source dialog for a Raw Data...

Define Finalization Settings for a Derived Dataset

The final step when creating a derived dataset is called the Finalize and QC step. It is optional, but provides important functionality. To edit the finalization settings, follow these steps. 1) Edit the derived dataset, and open the Finalize and QC panel. The panel displays any settings currently defined for the finalization step. Clicking the Delete button will completely remove any current settings. Clicking the Edit button will open the dialog to edit the settings. 2) The Finalize dialo...

Execute a Derived Dataset

To execute a derived dataset manually, follow these steps. To execute a derived dataset on a schedule or automatically, see https://support.lityxiq.com/373978-Execute-Raw-Data-Import-or-Views-on-a-Schedule. 1) Select the dataset in the Available Datasets list. 2) Click Execute Derived Dataset -> Execute Now from the Selected Dataset menu. 3) Click Yes to confirm or No to change your mind. 4) The dataset will execute shortly. To watch the progress of the view and any related mess...

Define Multiple Incoming Datasets for a Derived Dataset

When defining the Incoming Datasets for a derived dataset, you can define mulitple datasets at once using the Multiple Datasets button. Clicking this button, or editing an existing Multiple Datasets definition, provides options described below: All variables from all datasets will be included in the view. Method - currently, two methods for defining multiple incoming datasets are supported: - Manually Select Multiple Datasets - this option allows you to manually click datasets that you w...

Define a Single Incoming Dataset

When you click the Add Dataset button in the Incoming Dataset section of a derived dataset, or edit an existing Incoming Dataset, you will have a number of options related to how it is used. Dataset: A list of all datasets available to you appears in the topmost drop down menu. Find the dataset to be used as the basis for this view and select it (it will become checked once selected). Variables to Keep: In this area, you will select which variables will be brought in from the selected dat...

Creating a Dataset Library

Dataset libraries can be used to organize the datasets you create into logical groups. Creating a dataset library is an easy task. Click Libraries from the Data Manager menu. 2) Click the Create New Library button and enter a name and optional description for the library. Then click OK. The new library will appear in the list of available libraries. 3) When selecting the library from the list of available dataset libraries, you will have access to other options for the lib...

Quick Insights

Quick Insights is a way to summarize data quickly to gain insights. It uses the approaches available in Insight, but makes them available directly from the dataset in Data Manager. After selecting a dataset in Data Manager, select the Quick Insights link: You can select any of the available Insight types, such as Chart, Table, or Pivot. After selection, you will be provided with options similar to those provided in the Insight area of LityxIQ. With Quick Insights, you will not have the optio...

Comparison of Aggregation Types

In LityxIQ, two different types of data aggregations are available, which can be used in conjunction with each other. The two types can be described as: 1) aggregating over all combinations of the levels of the selected variables,. 2) aggregating over all individual levels of the selected variables. This document provides a comparison of these two techniques. Both area available in the Aggregation area of defining a View. Assume we have a very small and simple dataset with three variables: A...

Create a New Aggregation Field with Examples

The new aggregation field function will provide an aggregated value for every record in an existing dataset. This is a distinct feature as opposed to the “aggregate” function, which can change the number of fields and number of records in an existing dataset. See https://support.lityxiq.com/192161-New-Field-Aggregations---Concept-and-Comparisons for a more complete overview of New Field Aggregations. 1) In Data Manager select the dataset you wish to edit, and under the Selected Dataset tab clic...

Create and Utilize a Configuration Variable

Configuration Variables can be used as dynamic variables (sometimes thought of as macro variables) whose values can be used throughout multiple areas of LityxIQ. They allow you to configure parameters or inputs to a process that are easily changeable from one run to the next, so that you do not have to manually change the value in all places where it was used. They can also be used to track a process flow, or kickoff the execution of objects within a flow. There are three ways to create a Confi...

Executing or Scheduling a Dataset Export

To run a dataset export, you must first create export settings (see https://support.lityxiq.com/002683-Export-a-Dataset---Settings) (https://support.lityxiq.com/002683-Export-a-Dataset---Settings) Then, you have the option of executing the export manually, or scheduling it (including automating the export process). Executing a Dataset Export Manually Select the dataset from the list of available datasets, click the Selected Datasets menu button, click Export, then click Export Now. Y...

Export a Dataset - Settings

It is easy to setup a dataset for export, when you wish to take data from LityxIQ to upload into other applications. First, select the dataset from the list of available datasets in the Data Manager. From the Selected Dataset menu, click Export, then Export Settings. This will open the Export Settings dialog. Its four tabs are described below. Export Location Tab - Export Location - select the location where you would like to place the exported dataset. For example, this may b...

Viewing Available Datasets

The ‘View Datasets’ link within DATA MANAGER provides a list of all available datasets within a selected library. It is here that you can create new datasets and manipulate existing ones. After clicking the ‘View Datasets’ link, the working area in the right pane of LityxIQ will look like the following. Important areas are explained in more detail below. * Dataset Library - This drop down box displays a list of all of the dataset libraries to which you have access. Select the library...

Copying a Dataset

In LityxIQ, you can very easily make a copy of a dataset definition. This process does not create a copy of the data, just a copy of the settings for a dataset. You can use this copy as a template for creating a similar dataset that is, say, based on the same data or data dictionary, or making an exact duplicate of the data. Follow these steps: 1) Select the dataset you wish to copy in the Available Datasets list (it will highlight orange), and click Copy Dataset Definition in the Selected Da...

Defining an Aggregation for a Derived Dataset

After opening the settings dialog for a derived dataset (see https://support.lityxiq.com/241959-EditingDefining-a-Dataset-View), follow these steps to define an aggregation step. It is not required that a derived dataset have an aggregation defined. If an aggregation is defined, the resulting dataset will have fewer (usually many fewer) records than the original datasets. The number of records will be the total number of different combinations of the selected aggregation variables (see below). T...

Defining a Filter for a Derived Dataset

After opening the settings dialog for a derived dataset (see https://support.lityxiq.com/241959-EditingDefining-a-Dataset-View), follow these steps to define a filter for the view. A filter is a method for subsetting the rows of the dataset to match a given criteria. Note that it is not required that a derived dataset have a filter defined. 1) If the Filter panel is not already opened, click on that panel header to open it. Then, click the Edit button. Note that if this view had previously b...

Defining New Fields for a Derived Dataset

After opening the settings dialog for the derived dataset (see https://support.lityxiq.com/241959-EditingDefining-a-Dataset-View), follow these steps to define any new fields to be created. It is not required that a derived dataset have any new fields defined. 1) If the New Fields panel is not already opened, click on that panel header to open it. If new fields have already been defined for this view, as in the example below, you will see a list of all fields currently defined. Note that the ...

Defining Joins for a Derived Dataset

After opening the settings dialog for the derived dataset (see https://support.lityxiq.com/241959-EditingDefining-a-Dataset-View (https://support.lityxiq.com/926307-Using-the-Search-and-Filter-Dialog)), follow these steps to define any joins. It is not required that a derived dataset have any joins defined. 1) If the Joins panel is not already opened, click on that panel header to open it. Then, click the Add New Join button. Note that if this view had previously been defined, you may see inf...

Defining Incoming Data for a Derived Dataset

Incoming datasets in a view are those that are stacked together (a SQL-experienced person would call this a UNION) to begin the process of creating the view. After opening the Edit View dialog (see https://support.lityxiq.com/241959-EditingDefining-a-Dataset-View), follow these steps to define the incoming data for a view. If the Incoming Data panel is not already opened, click on that panel header to open it. Then click the Edit Incoming Data button. Note that if this view had previously be...

Editing/Defining a Derived Dataset

A Derived Dataset in LityxIQ is one in which one or multiple other datasets can be brought together and modified (joined, aggregated, filtered, transformed, etc) to create a new dataset. In order to define a derived dataset, follow these steps: 1) Select the dataset from the available datasets list, then select Edit Settings from the Selected Dataset menu. If the dataset was just created using the Create New Dataset button, this will open automatically and you can skip to step 2. 2) Th...

Creating a Derived Dataset

To create a new derived dataset in LityxIQ, follow these steps: 1) In the Data Manager, click the Create New Dataset dropdown menu and select Derived Dataset. 2) The New Derived Dataset dialog box will appear. Enter the name you would like to give to the dataset, and optionally provide a description of the dataset. These will both appear in the list of datasets. The name you enter must not be the name of an already existing dataset in the active project. You can also specify the library ...

Create New Dataset

To create a new dataset in LityxIQ, follow these steps: 1) Select the dataset library in which you want to create the new dataset. 2) Click the Create New Dataset dropdown and select either Raw Data Source or View, depending on how you want to create the dataset. Each option is described in more detail separately. 2) In either case, a dialog is displayed that lets you give a name to the dataset as well as a description. The name must not be the name of an already existing dataset in...

Define the Source Settings for a Raw Dataset

To import a file or other external data into LityxIQ (to create a LityxIQ dataset), you must define the source settings for the dataset. The dataset in LityxIQ is referred to as a Raw Dataset because it points to and imports data from an external raw data source. That data source may be a file on an FTP site or S3, a database connection, a file uploaded directly into LityxIQ using the File Manager (see https://support.lityxiq.com/125826-Uploading-a-File), or data in a CRM system (among other pos...

Import Data into a Raw Dataset

To import or load data into a raw dataset manually, follow these steps. To import data on a schedule or automatically, see https://support.lityxiq.com/373978-Execute-Raw-Data-Import-or-Views-on-a-Schedule (https://support.lityxiq.com/373978-Execute-Raw-Data-Import-or-Views-on-a-Schedule?r=1). 1) Select the dataset in the Available Datasets list. 2) Click Load Data -> Load Now from the Selected Dataset menu. 3) Click Yes to confirm or No to change your mind. 4) The import process...

Browse a Dataset

After a dataset has been created and the data loaded, the raw data it contains can be browsed. Simply select the dataset in the Available Datasets list, and click Browse Data in the Selected Dataset menu. The data browser window will open. The options available in the browser are explained below. When finished browsing, click the X in the upper right corner of the browser window. - Clicking on the ‘^’ carat on a column reveals a set of options for that column, including sort o...

Viewing Summary Statistics for a Dataset

When data is loaded into a dataset, LityxIQ will automatically create summary statistics for each variable. The summary statistics can be viewed by following these steps: 1) Select the dataset in the available datasets list (it will highlight blue gray), then click ‘Summary Statistics’ in the ‘Selected Dataset’ menu. 2) Results will appear in a new window. See below for descriptions of the information provided. Click Done when finished. - The statistics provided include: ...

Execute Raw or Derived Datasets on a Schedule

It is possible to import data or execute a derived dataset automatically when LityxIQ has detected a need for a refresh (this is called Upon Data Refresh), or on a date/time-based schedule. Follow these steps: 1) Select the dataset in the Available Datasets list, then select Load Data -> Schedule It from the Selected Dataset menu. In the case of a view, the menu will show the option Execute View instead of Load Data. 2) The Scheduling dialog will appear. See https://support.lityxiq....

Deactiving, Re-activating, and Deleting a Dataset

Deleting a dataset in LityxIQ is a two-step process (to guard against accidental deletion). It involves deactivating the dataset and then deleting it. Use caution when deleting a dataset. Once deleted, a dataset cannot be recovered. Deactivation is a way to temporarily disallow operations with the dataset, while keeping it intact. To deactivate a dataset: 1) Select the dataset in the Available Datasets list (it will highlight blue-gray), then click Deactivate in the Selected Dataset menu...