Helpful Hints

Home > Data Manager > Helpful Hints

Create an Automatically Updating Cumulative History Dataset

It is easy in LityxIQ to create a dataset that contains an ongoing cumulative history of records, and optionally automatically refreshes itself. For example, you may be required to maintain a history of all processed records, such as an ongoing campaign contact history. You want that history to automatically update (a cumulative update) when a new campaign file is processed. The three steps are simple and are described below. Step 1: Create View that will maintain the Cumulative History a...

Examples of Splitting Strings with SPLIT_PART

The SPLIT_PART function is a helpful way to extract parts of strings that have delimited data embedded. For example, suppose you have a string in a variable "Full Address" with data like the following: '123 Main Street, Apt 1' The objective is to create two variables from this string, one with the "Street Address", and another with the "Address 2" component. One key aspect of being able to perform this kind of operation consistently and correctly is knowledge of how the initial string is str...

Using Quotes Correctly

Specifying literal string values within New Field code or within Filters is a common need. Below are some rules and tricks to ensuring you do it correctly. Use Single Quotes to Delimit Literal Strings in Your Code Literal string values that you need to reference in your LityxIQ new field or filter code should be delimited with single quotes on either side. For example, SUBSTRING('ABCD', 1, 2). If you require a single quote to be within your literal string, you can "double it up", meaning p...

Generating Random Numbers

The RANDOM() function will return a random number uniformly distributed in the range 0.0 <= value < 1.0. You can use this as a part of new field definitions. To generate random values between other start and end points, such as generic numbers labeled L and U (lower and upper bounds), you can use the code: L + (U - L)*RANDOM()

Zero in Denominator Errors

When performing a ratio calculation, if your data has any situation where there would be a zero in the denominator, the code will produce an error when executed. The fix for this is to use the NULLIF function in any denominator that could be zero when evaluated with actual data. For example, the following code: [numerator] / [denominator] can safely be replaced with: [numerator] / NULLIF([denominator], 0) This will return a NULL value into the resulting dataset for cases where the denominat...

Taking Care with Integer Arithmetic

LityxIQ interprets integer division using the "/" division sign literally. Care needs to be taken to ensure you get expected answers. For example, the code 3/4 will return 0 because this is interpreted as integer division. To ensure that ratios are correctly computed when using integers as input, at least one of the numerator or denominator needs to be interpreted as a decimal value. So, the above code can be updated using 3.0/4 or 3.0/4.0 or 3/4.0 to give you the correct decimal output of 0.75...

Simple Searching for Substrings

Two functions are useful when you need to find simple substrings within other strings The two functions are CHARINDEX and STRPOS are identical except for the order of the parameters you pass to them. Both functions return the position of the found string within the initial string, or return 0 if it was not found. Example: Find the string 'DE' within the string 'ABCDEF' Option 1: CHARINDEX(‘DE’, ‘ABCDEF’) Option 2: STRPOS(‘ABCDEF’, ‘DE’) Both return the value 4 since 'DE' is found starting at...

Using the String Length Setting When Creating a New Field

The String Length setting when creating a new field can be used to ensure that the resulting data type of the definition is "string", and also ensure that the length is a certain value. Typically, LityxIQ can figure out the field type and length on its own. But there are a couple of instances where it will not always get it correct, and this is a way to help it along. Two common cases where setting the String Length is recommended are: CASE/WHEN Statements Because evaluating the resu...

Regular Expressions and Pattern Matching

The capabilities for finding patterns in strings is very powerful using a concept called "Regular Expressions", which is often abbreviated REGEX or REGEXP. Regular expressions allow for very flexible and complex patterns to be described and found in strings, and the related LityxIQ functions allow you to find, replace, count, and perform other operations on matched patterns. Regular expressions are very hard to learn, but can be very powerful. We recommend online resources such as https://regex1...

Extracting Date Components

Extracting components from date variables (such as the year or the day of week) can be extremely important in some projects. The key functions that help with this are EXTRACT and TO_CHAR. Which is best depends on what you are trying to do. EXTRACT will always return a number, while TO_CHAR will return a string. Here are some examples. In each example, assume there is a date variable named “mydate” storing the date May 17, 2017. Desired result EXTRACT function TO_CHAR function ...

Potential Errors Importing Numeric Fields with Non-Numeric Data

When importing an external data file or from a database, if the file or database table has non-numeric data (such as non-numeric characters such as "ABC") in fields marked as integer or decimal in the dataset dictionary, LityxIQ will do its best to identify that issue and fix the problem. Here are some helpful hints: 1) For numeric fields (integer or decimal), non-numeric data in the field will result in NULL values - Blank or empty values in the import file will load as null, and you can ...

Coalesce Function

When appending multiple datasets into a single large dataset, there may be a situation where each individual dataset contains a slight variation on a single field. For example: a field containing zip codes carries the similar names of “zip”, “zipcode”, and “Zipcode” in the various incoming datasets. The append operation will result in three different variables in the new dataset. This may cause many records to having missing values for those fields not in their original dataset (this is standard...