Databases‎ > ‎

Google: Fusion Tables - Orders

Import the Orders data file

Download the file (DB 2 Order Import File - Data.csv) attached to this page.

The file looks like:
Customer,City,Date,Product,Sales,Payment Method
Allied Textiles,Halifax,2014-01-13,Nylon,498.00,Credit
Allied Textiles,Halifax,2014-01-20,Nylon ,498.00,Credit


Create a new Fusion Table by importing the .csv file from your computer.

Add a Summary

A summary can hold more than one for these queries.
Queries created by a summary tab can be more complex.
  1. for each customer, determine the total Sales
  2. for each customer, determine the average Sale
  3. for each Payment Method, determine the total Sales
  4. for each Product type, determine the total Sales value
  5. for each Product type, determine the average sales value
Rename your Summary tabs to describe the information it contains

Note:
  • Summary tabs only display 10 elements in the chart, all the data is on the left hand side of the window

Add Multiple Filters

Add a row layout -- click on the red plus sign.

This will create a second copy of all the records
 
Once a field is selected to be filtered, you can choose to include or exclude values  

Multiple filters can be added to perform more complex queries.
  1. All cotton orders between $500 and $1000 that were purchased for cash 
  2. All silk credit orders that are over $500 
  3. All silk orders over $500 not sold to Perimeter Fabrics 
  4. All cash orders over $400 except for cotton ones. 
  5. All purchases not made by General Weavers and with a selling price of less than $1500 and greater than $500. 

  6. All orders between $700 and $1650 
  7. All purchases made before Jan 15 with a selling price less than or equal 2100 
NOTE:
Once a filter has been added to a row layout and a new Summary Layout is added, the filter is also applied to the Summary Layout. This filter can be remove without remove it from  the original Summary Layout. 
Once a filter has been added to a row layout, it can be exported as a .csv file for further processing.
This type of query is harder to keep as each require its own layout to remain active.

Notice when you create a filter, text similar to the following appears beside the filter tab:
            'Payment Method' = 'Cash' AND Sales >= 400 AND Product NOT EQUAL TO 'Cotton'

This text is part of a general database language called SQL. It is used in many database programs to manipulate the data contained in a database

More Difficult Queries

this type of query is difficult to do:
  1. All cash purchases that have a selling price under $350 or over $1500 
    a new calculated field is required, the formula would be:
    if(Sales >1500 ,0,if(Sales<350,0 , 1))

ċ
DB 2 Orders Import File - Data.csv
(2k)
M. A. Vandepoele,
3 Jun 2014, 18:49
Comments