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 also set the string that be considered as null in the import settings if there is a specific string to be treated as null.
- The specific characters "," (comma) and "%" (percent sign) will be removed from fields labeled as integer or decimal in the dictionary as part of the import process, so that only the number part of the data remains.
- An imported field that contains other non-numeric characters in the data will result in a NULL value in the dataset for that record.
- Scientific notation is supported, such as fields with values like "1.56e+3" or "37.18E-3".
- The size of integer data imported, as noted in the dataset's dictionary, is important. LityxIQ will modify the dictionary if necessary to accommodate larger integer values that it finds compared to what had been specified by the dictionary.
- The range of the largest possible integer values that LityxIQ can store is -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. If you have values outside of that range, you will receive an import error. You should use the "decimal" data type for such fields.
- If you are still having trouble importing numeric data because of strange values in the source dataset, a workaround is to load the variable as a string (with enough length to hold the longest possible value), then convert it to an integer or decimal value in a New Field definition of a derived dataset as in the following examples:
- For integers: str_to_int([variable])
- For decimal values: str_to_decimal([variable])
2) For integer variables, if the import file has a value that is too large for the integer format that was set, the import may throw an error.
Make sure dictionaries are properly set for integer fields. LityxIQ will attempt to fix the problem and modify your dictionary to account for larger values in the imported dataset. If you still have problems importing data data, you can always set the dictionary for integer fields to contain the largest possible value, although in general this can lead to inefficiencies since more space is being used than necessary. But it is a good workaround if you are having trouble getting integer data to load properly.
3) For string variables, if the import file has an empty string, it will load as NULL.
This will not throw an error, but is good to be aware of this.
In the case of situations (1) and (2), if LityxIQ cannot fix the error in the underlying dataset, you may receive an error message such as "error attempting to auto convert numeric fields". In this case, please review your dataset dictionary, and review the raw file or database table to identify the issue at the source system.