3. Data - Select

Here you decide which ones of your data will be enclosed into your Data Definition. So, this is the area where you select and refine the data you want to analyse.


Contents



3.1 Selection


Different sources lead to different ways to select your data:


From a spreadsheet


After you have loaded your spreadsheet, the window Results opens showing your current Data Definition.


Designed to be easy: Smart Selection makes your data selection quick and easy:

  • SelfBi recognises the filled cells among the empty ones and selects the most suitable ones automatically.
  • If there are more data areas in your spreadsheet, SelfBi automatically selects the most suitable one from the first sheet.

If the selected data respond to your needs, refine your Data Definition.

But if you want to modify the selection of the current Data Definition, just click on Edit data selection to manually select the data you need. How? 

  • untick the option Extend Selection verticallyand select the data area you need, or
  • highlight only the name of the columns you want to select and tick the option Extend Selection vertically. In this way SelfBi vertically selects all data of these columns until it finds at least 2 empty consecutive rows in the source document.
    If you click on  , you find the option Stop vertical selection after ___ empty rows to decide how many empty rows are required to end the selection.

In both cases, indicate if the first row of the selected area contains the titles by putting or removing the tick on Selection includes titles. To reset the Data Names to their original file name, click  .

 Remember: if you want to select data from another sheet, click the tab named like the sheet from which you want to select the data.

When you finish your adjustments, click on Confirm selection. Anyway you can adjust your data selection again by clicking Edit data selection

Now you can refine your Data Definition.


From clipboard/notes 


After you have pasted the clipboard or notes, the window Results opens showing your current Data Definition, which consists exactly in what you had previously copied. 

If the selected data respond to what you want, refine your Data Definition.

But if you want to modify the selection of the current Data Definition, just click on Edit data selection to manually select the data you need. How?

  • untick the option Extend Selection verticallyand select the data area you need, or
  • highlight only the name of the columns you want to select and tick the option Extend Selection vertically. In this way SelfBi vertically selects all data of these columns until it finds at least 2 empty rows (Empty rows to stop) in the source document.
    If you click on  you find the option Stop vertical selection after ___ empty rows to decide how many empty rows are required to end the selection.

In both cases, indicate if the first row of the selected area contains the titles, by putting or removing the tick on Selection includes titles. To reset the Data Names to their original file name, click  .

When you finish your adjustments, click on Confirm selection. Anyway you can adjust your data selection again by clicking Edit data selection.

Now you can refine your Data Definition. 

From a database (SelfBi Desktop and Server only)  

In the Results panel there is your current Data Definition. If you want to change your query, click on Go back to Query and modify it. When you are ready, click on Go to Real-Time Results to see your current new Data Definition.

 Remember: while you are modifying your query no changes are made to your Data Definition until you click the button Go to Real-Time Results.

Now you can 
refine your Data Definition.

Watch the video

Connect your database: SQL

From the Join


In the Results panel there is your current Data Definition. If you want to change your record settings click on Go back to Data Definitions choice and modify them. When you are ready, click again on Go to Real-Time Results to see your current new Data Definition.

 Remember: while you are modifying your record connections no changes are made to your Data Definition until you click the button Go to Real-Time Results.



From the Library


When you choose a Data Definition from the Library, different windows can open depending on the original source of the Data Definition you have chosen.

  • if its source is a spreadsheet or clipboard/notes, the window Select data opens: modify the selection (if you want to) and then Confirm selection.
  • if its source is a database, the window Set your query opens: modify your query (if you want to) and then Go to real-time Data results.

 

WARNING: if you are modifying a Data Definition while you are using it in an analysis, you are changing its copy. If you want to change the original Data Definition stored in the Library, you have to modify it in the section Library.

Now you can refine your Data Definition.  

Watch the video

The Library overview

3.2 Refining



When you load or connect a source, the window Results  opens showing your Data Definition.

The columns you see are the fields of your Data Definition, called Data Names.

If you want, you can change the order of the columns by drag-and-dropping them to the left or to the right. You can also arrange data of these columns in increasing or decreasing order by clicking on the arrow beside every column title.

To the right side of this window, there is the panel Refine Data: here you can choose how to refine the values of each Data Name. This operation is very useful, because in this step you decide the default settings of your data, avoiding wasting time later on.  

Select a column and refine:

  • Name: you can't modify it. It is the default column name given by Selfbi.
  • Data Name: the name which identifies the single column in every process where this Data Name is involved.
    If you want to reset the Data Name, click Edit data selection and then click  , so that you will be able to see again the data columns named as in the original file. 
  • Type: any, string, integer, number, boolean, date, time.
  • Align: left, centre, right (by default text is aligned to the left and numbers to the right).
  • Format: none, default, accurate, normal, integer, with decimal without thousands separators, integer without thousands separators, thousands in "kilo", millions in "Mega", thousand in "kilo" without decimals, millions in "Mega" without decimals.
  • Round: if ticked, you can choose the rounding unit, the rounding type (mathematic, round down, round up), symmetrical rounding.
  • Convert from: this option is related to the type of data. If Selfbi doesn't recognize the original type of the data, here you can set it manually.
  • Function: none, average, distinct average, count, distinct count, maximum, minimum, sum, distinct sum.
  • Values handler: it allows you to decide which values must be taken into consideration when aggregating Data. In order to consider them when aggregating data, tick one or more of these check boxes:

    • Not null values: consider all the values except null (empty cells) 

    • Null values: consider only null values (empty cells) 

    • Not equals 0: consider all the values which are not equal 0

    • Equals 0: consider only values equal 0  

  • Formula: you can add a new column containing the result of a mathematical formula. You'll find more details at the bottom of this chapter.
  • Description: you can write a text describing the column.

If you want to refine any other column, select it or move to it using the arrows beside its title in the Refine Data panel.

 Remember: you have set the default settings. Later do you want to modify this settings? Yes, it is possible! you can modify them whenever you want in Edit.

Watch the video

The Data Refining

If you click on  , you add a Calculated Field to your Data Definition: by writing a mathematical formula in the panel Refine Data you relate two or more fields of your Data Definition. If you don’t need your calculated field anymore, just click   and delete it.

WARNING: you have to write the formula in capital letters, otherwise it won't be recognized!

When you finish all the adjustments, click on Close to start working on your analysis. Now you are in Edit.