Logical Operators

Chapter2 Organizing your Data

Since you will create more views in the coming sections, you need to organize these views in order to easily access them.

SuperQuery main screen consists of one row of eight tabs. You can edit the tab title and change the tab color. You can explore these features from the tab property menu. To display the property menu, click the right mouse button on the tab you want to edit.

From the property menu of the first tab, select the "Title" menu item and write down " Sales Data" in the "Edit Title" edit box, then press OK. Repeat for the second tab, and write down the title "Product Summary".

Now we need to copy the main data view and put it in a separate tab before performing more analysis. Press the Duplicate button. SuperQuery creates a new copy of the same view. To move this view to the third tab, press the button. The button state will change to indicating that the view is now floating. Click on the third tab, in order to activate it. The current active tab becomes the third tab and the view still floats on this tab. Press the button again. The view is now fixed in this tab and the button state changes to . Write down the title "Raw Data" on the third tab in the same way you did for the two previous tabs.

SuperQuery's data view consists of four panes; the main pane is the Data Table that controls the scrolling of other panes. The other three panes are: Total Page, Reps Graph and the Data Page. Each pane has a re-sizable frame, to allow you to change pane size according to your preferences and the type of the analysis you are performing.

As you move the mouse on the pane frames, the cursor changes to in order to re-size vertical splitters and to re-size horizontal splitters separating the panes. As you move the cursor towards the splitter ends, the cursor changes to one of the following shapes in order to maximize or minimize the pane in the arrow's direction.

To change the pane type simply, select the "Change Type" menu item from the pane property menu. To display the property menu, click on the right mouse button when placed on the pane you want to edit. Select the new pane type from the list of the available types. Each pane has its own unique property menu that is tailored to its own functionality.

It is recommended that you spend some time to familiarize yourself with the different pane types and their properties in order to explore the strengths and capabilities of the system.

Chapter 2: Organizing your Data

Chapter3 Querying the Database

Now that you have the view of the table "Sales", you can start browsing, exploring and learning more about your data.

SuperQuery Table View contains several panes separated by Splitters. As you are scrolling in the Data Table pane, all other panes in the same view are updated at all times. You can completely reconfigure any view by including some or all pane types.

Simple Queries:

In the Sales view:

Click on the "Sales Data" tab. Move the cursor through the columns, and look at the Reps Graph to see the most common values in each column.

Go to a numerical column "PROFITDLR" or "QTYSOLD" and look at the Total Page pane to see the total, average, minimum and maximum values.

Click on any cell in the "CITY" column. The Reps Graph pane will show the most common values in this column. Double click on the "New York" bar in the graph. SuperQuery will immediately position the cursor in the Data Table at the first row that has the value "CITY = New York". Now push the filter button, and you will see only those rows satisfying that value "CITY = New York". Scroll through the other columns and watch all graphs and statistics calculated for New York. Furthermore, move to the "Profit" column and place the cursor on any cell having the value "High" then press the button again. This will simply perform the Query "City = New York AND Profit = High". This means that you are only viewing sales in New York City, which has high profits.

As you may have noticed, both "Profit" and "Quantity" columns have the cyan color, to reflect that they are calculated by the system. The Analysis Wizard created these two columns when you were reading the data table.

In order to edit the "Profit" column; first make sure that no filters are set on the view. To release all filters, press on the button, so that the green light is turned off. Double click on the column "Profit", the "Enter Ranges" dialog is displayed. Click on "5 Range" button and press OK. The column will be recalculated to map the original numerical values into five ranges (Very High, High, Medium, Low and Very Low) instead of three ranges (High, Medium, and Low).

More Comprehensive Queries:

In the "Sales" table, suppose that you want to study the New York City sales only during the winter. To do this, you need to create a new column of type "Filter" to perform the criterion: CITY = New York and MONTH is one of (December, January and February).

Click on the first tab "Sales Data" in order to work with the Sales data view. Make sure that no filters are set on the view. To release all filters press on the button, so that the green light is turned off.

Place the cursor on the column " CITY" and press the button. The Add / Edit Virtual Columns dialog is displayed. Type the new column name "NY Winter Sales" in the "Select / Add column" edit box. Press on the "Filter" button. The "Enter Criteria" dialog is displayed.
To create the first part of the criteria "CITY = New York" double click on the cyan cell next to "CITY", the Enter Criterion dialog is displayed.
Click on the drop-down box to select the operator "=". Select the value "New York. Press OK to return back to the "Enter Criteria" dialog.
In order to AND another criterion, simply double click on any other cyan cell in the same column that has the previous criterion you created. On the other hand, if you want to OR ANOTHER criterion, double click on any other cyan cell in the same ROW. Now to AND the second part of the criteria "MONTH is one of (December, January and February)", double click on the cell next to the "MONTH", again the "Enter Criterion" dialog is displayed.
Click on the drop-down box to select the operator " is one of". Select the values "December, January and February" from the list and press OK. The Enter Criteria dialog will show the criterion " is one of (December, January and February)" in the cell next to the "MONTH". Press OK to return back to the Enter Criteria dialog. Press OK.
SuperQuery will create the new column "NY Winter Sales" and add it to the view before the "CITY" column. The new column will be given a cyan color indicating that the column is a calculated column (Virtual Column).
On the graph double click on the "True", the cursor will be placed on the first value equal to "True" in the "NY Winter Sales" column. Press the filter button to show only the values satisfying the criteria. Now move the cursor on the column "QTYSOLD" and "PROFITDLR" and study the statistics of each column from the "Total page" pane.

Chapter 3: Querying the Database

Chapter4 Discover Facts and Rules.

Now you are ready to run the most powerful feature in SuperQuery and discover the patterns hidden in the data.

Click on the first tab "Sales Data" in order to work again with the Sales data view. Make sure that no filters are set on the view. To release all filters, press on the filter button, so that the green light is turned off.

Press the Facts button to display the "Fact Discovery Engine" dialog.
In the "Select / Add Fact Table" edit box, write down "Profit Facts" to name the new Facts table. SuperQuery will store all the discovered facts and exceptions in this table.
From the "Then conclusion" drop-down list, select the Profit column.
Because the example data table is a small table, we need to change the settings of the Fact Discovery Engine to increase the number of facts discovered. Press the "Settings…" button.
The Fact Settings will display. Move the first blue slider to the right so that it shows 90% at the right side. Move all other blue sliders to 80%. This means that you are tuning the engine to discover more facts.
From the "Maximum Fact Level" drop-down box, select 2.
Press OK to exit this dialog.
In the "Fact Discovery Engine" dialog, press Run.
Once the "Run" button is pressed, SuperQuery starts the Fact Discovery Engine and displays the "Facts Progress" dialog showing facts as the engine discovers them. The dialog gives some useful information about the engine performance as it discovers the facts.

When the engine finishes scanning the data , it asks you if you want to view the discovered facts in a table view. Press "Yes" to view the facts in the SuperQuery Facts View.

The new view will be floating on top of the data view. You can move this view to one of the empty tabs and edit its title to read "Profit Facts" (as explained in Chapter 2).

Now you can scroll through the "Fact Table View" rows and read the facts. Notice that as you are scrolling in the rows, the Facts pane (the pane below the Data Table pane) shows the description of the current fact in natural language. For example, in the first row, you should have a fact similar to following:

Type	Percent	IfColumn	IfValue	ThenColumn	ThenValue	No. Rows	Level	Time
Most	80	PRODUCT	Hat	Profit	Medium	25	1	17:34:19

Type	When SuperQuery presents the facts, it uses the words "Most" and "All" to differentiate between facts that were discovered and applicable to all data rows, and those facts that are applicable only to the majority of the data rows. In case of exceptions, the word "Only" is used.
Percent	This is the percentage of rows that matches and meets the discovered fact to the total number of rows that meets the "IfValue".
IfColumn	Condition column name
IfValue	Condition value
ThenColumn	Conclusion column name
ThenValue	Conclusion value
No. Rows	The number of rows supporting this fact.
Level	The depth of the search level used to find this fact. Level 1 means the engine considered only one column in the condition side to find this fact.
Time	The time the engine found this fact.

This Fact means that "Most Hat Sales have Medium Profits." The fact has a certainty factor of 80% and there are 25 rows supporting this fact.

You can also look at the Facts pane and read the same fact in a more natural language "Most Sales where PRODUCT = Hat --> have Profit = Medium. Percentage 80% Supported by 25 rows." You can also read the fact in the Rules format. To do this, click on the Right mouse button when placed on the Facts pane and check the Rules Format. The text will be changed to "If PRODUCT = Hat --> then Profit = Medium. Confidence 80% Supported by 25 rows."

Press the Drill-Down button to view those data rows supporting this fact. SuperQuery will create a new data view and set filter on those data rows. You can close the view or move it to another tab.

It is sometimes easier to read the discovered facts in a report form. SuperQuery allows you to export the discovered facts into text files in both Facts and Rules format. From the Table menu, select the Export | Facts menu items. When the "Select Table" dialog is displayed, select the "Profit Facts" table. Press OK, the Save As dialog is displayed. Write down "ProfFact" in the "File name" edit box and press OK. SuperQuery saves the new file as a text file "ProfFact.txt". Open this file using any text editors and view the text. Try to do the same steps to export the Facts Tables in the Rules format and compare the two.

In situations where you have a lot of facts, it is useful to be able to set filters on specific types of facts and also see a graph of the distribution of facts among columns and values. To do this, you need the "Reps Graph Pane" which is already in the view but minimized. Use the splitter bar at the right side to resize it, then scroll through the view columns to see the different distributions.

Chapter 4: Discovering Facts and Rules

Chapter 5 Summarize your data

Suppose that you want to analyze your sales by product. For example, for each product, you want to know:

The most common city and month in sales,

The total quantity sold, and

The total profit.

Now click on the first tab "Sales Data" in order to work again with the Sales data view. Make sure that no filters are set on the view. To release all filters, press on the filter button so that the green light is turned off.

On the sales view, press the Summary button; the Add / Edit Summary table dialog is displayed.

In the Select / Add Summary Table edit box, write down "Product Summary" to name the new Summary table. Click on the "Group By" drop-down box to select the PRODUCT column. Press the "Select Cols" button in order to select the columns City, Month, QTYSOLD and PROFITDLR and their associated summary operators. The "Select columns and their summary calculations" dialog is displayed.

Check the boxes next to the columns: City, Month, QTYSOLD and PROFITDLR. Click on the drop-down box next to the "CITY", and select the operator "Most common". Also for the "MONTH", select the operator "Most common". For both "QTYSOLD" and "PROFITDLR", select the operator "Total". Press OK to return back to the Add / Edit Summary Table dialog and press OK.

SuperQuery will summarize the data on each of the products: Coat, Glove, Hat, Jacket and Shirt and will show them in a new Summary view.

The new view will be floating on the top of the data view. Move this view to third tab "Product Summary". To do this, click on the "Product Summary" tab. The view will move to this tab. Press on the button to fix the view in this tab.

The first row in the summary view reveals the following :

Boston is the most common city in coat sales.
December is the most common month in coats sales.
The total quantity sold for coats are 7260.
The total profit for selling coats are $13150.

In order to view those rows that have "PRODUCT = Coat", place the cursor on the first row and click on the Drill-Down button. SuperQuery will create a new data view and set a filter on those data rows. You can close the view or move it to another tab.

The Data Graph is showing the values of the column you have the cursor on. In this case, the graph is showing the values of the column "Total QTYSOLD". It is clear that Shirts are the highest quantity sold since they have the longest bar in the graph. You can set the Graph Pane to show the highest or lowest values using its property menu.

Browse through the summary view rows and columns and look at the Data Graph and experiment with changing the property of each pane.