Managing Data
Omniscope easily imports tabular data, but making the most of the data in Omniscope sometimes requires modifying the names of the fields (columns), the layout (how data is arranged or divided across columns) or orientation (which are columns and which are rows) of the data in the Omniscope file. Omniscope offers many powerful data manipulation features to help you make these changes. These modifications are internal to Omniscope and some changes (such as re-naming and hiding) can survive refreshing from the original linked source. Other data modification operations must be repeated after each refresh if the source data layout or orientation cannot be changed.
Warning: Omniscope does not yet support partial refresh of only certain columns from linked data sources. If you add columns which are not present in the original linked data source (such as map coordinates or image references) to your Omniscope file, these columns will be lost or overwritten if you need to refresh from source. To preserve your added data, keep all columns not found in the source file in a separate merge file, with a common column of references so you can join the files together after each refresh. For more information, see Refreshing Data and Merging Data.
Managing Fields (columns)
Omniscope permits you to add, remove, hide and rename fields (columns) and to convert field data types using the Edit > Manage fields dialog. Database field names are often not self-explanatory, so renaming some fields is often a good idea, as is hiding columns you use, but not displayed to users of your file. Removing columns neither you nor your users will need will make your file smaller, but refreshing from source will restore the removed columns.
If you wish to replace a string of text in one or more columns with a different string, you can use the Replace within values option. If you want to calculate (and re-calculate) results in a column, perhaps applying additional logic tests, you can convert the column into a Formula field. For more information, see using Edit > Manage Fields.
Managing Values within fields
Data is imported from source may have either too much or too little information in each cell. If there is too much (first name, middle name and last name in the same cell), you may want to split the values from one column across several new columns. If there is too little (the first 4 digits of a post code in one column and the rest in another), you may wish to collapse the values in multiple columns into a single column. Sometimes, you may want to add a prefix or suffix to all the values in a column.
The Table View Tools sub-menu contains Collapse values (reduce to one column), two kinds of Expand values (spread across more than one column), and Append/Prepend text to values options which allow you to change the layout of your data across columns, or add defined prefixes or suffixes to values in selected columns. For more information, see Using the Table View.
International issues
When importing data by opening a .CSV or Excel .XLS file, Omniscope analyses the data in the file to determine how the data is arranged and which fields (columns) are most likely of data type Text, Numbers, or Dates & Times. The regional settings in your computer's operating system (Windows, or Mac, etc.) have a strong influence on this. For some combinations of settings and data formats, Omniscope cannot reliably detect the situation automatically.
.
If you find your data opens incorrectly and your fields are not correctly structured, your numbers have the wrong decimal place, etc. consult the File > Open file...>Open Data Set wizard page, which contains options to help with some common issues. For more information, see International issues.
Specifying custom date formats
Omniscope supports Date & Time data typing. Most common Date & Time formats are recognised automatically on import. If you create a new date field (column), or Omniscope does not recognise the format in your imported data set, you may be asked to provide guidance on interpreting the format, and to specify how the imported values should be displayed. The Date & Time formatting wizard will appear, or can be accessed from the Edit > Manage Fields dialog whenever changing a data type to Date & Time:
A date format is a sequence of case-sensitive characters describing the format of date/time values, not the dates themselves. For example, to show a dates such as "16-Mar-2002" you would specify the date format "dd-MMM-yyyy" in Omniscope. You can specify a wide range of formats, including days of week and time zones. For more detail and complete discussion of date and time formatting options, see Dates & Times.
Tokenized data- multiple values per cell
Sometimes, more than one value is associated with certain attributes of a record. For example, shoes can be available in more than one size, or you may have more than one image associated with a house (or whatever). Items available in more than one size, or with more than one associated image are examples of many-to-one relationships. Omniscope handles these kinds of relationships with a data subtype for Text and Category data fields called 'Tokenized'. In the example below, each record (row) is a pair of shoes, each of which is available in more than one size.
By declaring the filed (column) 'Size' to be tokenized, we can enter more than one size in the cell, divided by commas (or any separator you choose). Users of this file can specify their shoe size, and Omniscope will show only those records available in that size. For more detail, see Using Tokenized data.
Adding map coordinates
There are currently three ways to add geographic decimal coordinate (longitude and latitude) columns to your data for map displays. One option is to locate your records in Omniscope using the free Visokio world, country and city maps- just place the markers manually to generate the coordinates automatically as shown below (see the tutorial on Using Maps).
Alternatively, you can also look up missing coordinates using online sources such as Google Earth (and often Wikipedia), both of which can be integrated into Omniscope work flow as web services links. Just look up the missing values, then type or cut-and-paste them into Omniscope, copying the same values down for all the records in the same location.
If you have county or city names in your file, you can also merge geographic coordinate columns into your Omniscope data automatically using a
merge file. To merge in coordinate columns for capital cities or geographic centres of countries, we supply a free
'By Countries' merge file containing these columns. We also supply a free
'By Cities' file containing coordinates for about 60,000 of the world's largest cities. Free merge files '
By Post codes' are available for the UK and the U.S., and an increasing number of other countries. Other free and licensed reference merge files may be available from other suppliers in future.
To learn more, see the User Guide section on
Using the Map View, and the Knowledge Base sections on
Maps & Coordinates.
Data Orientation- Time series
Omniscope processes repeated observations of values over time in vertical columns, rather than horizontal rows often used in spreadsheets. In the example of a time series data layout below, we have various bonds, each with a reference ISIN identifier in the first column. We also have repeated observations of Dates and Values (Price/Yield) over time entered vertically down the Date and Values columns. Each record (row) in the data is therefore a separate observation of the Values (Price/Yield) on a given Date repeated over time 'vertically' in separate rows:
Time series data layout in Omniscope requires at least one field (column) with a natural order, such as Date, and one or more values columns 'Price', 'Yield', etc. for repeated observations of values that make up the time series. In addition, to have multiple curves on one graph, there should be a Category field (with less than about 200-250 unique values) which contains the names of each curve to be drawn. In this example, we have many observations, but only 9 individual ISINs (bonds), so the curves can be plotted by the Category 'ISIN' reference field as shown in the example above. Don't worry about the apparent repetition in the data values. Outside of the Table View, Omniscope will render this invisible to the users of your files. For more information, see Displaying Time Series .
If your data is arranged differently from the example above, (e.g. 'horizontally' with the dates as columns and each cell in the row representing an observation over time) you may want to use the the tools available under Edit > De-/Re-Pivot on the Main Toolbar to transform your data set to the correct 'vertical' orientation in Omniscope (see below).
Changing Data orientation using De-Pivot/Re-Pivot
Omniscope provides powerful tools to change the orientation of the data from rows to columns, or from columns back to rows. This can be useful when working with data in Omniscope, and also to change the orientation of data being exported from Omniscope to other applications.
For more information, see Using De/Re-Pivot
Merging Columns and Rows from other files
Omniscope enables you to assemble data sets from multiple sources by merging, adding either more columns or more rows to your Omniscope data sets. You can merge data from other .IOK files, or from .XLS, .CSV, .TSV data files, or from database views/tables using the Merge Data wizard accessed from File > Import to current file > Merge data from another file:
Merges can be either a 'Join' where new columns are added to existing data using values from a common column (one to one, one to many or many to one relationship), or a 'Concatenate' where new records are added to existing columns (column names must be the same). Joins can use multiple criteria, and you can choose to import non-matching records, etc. For more information, see Using the Merge Data wizard.
Back to Configuring your Data
User Guide Top
