Walter Hildebrandt wrote:
On Mon, Oct 5, 2009 at 9:49 AM, Andreas Saeger <[email protected]>wrote:
Walter Hildebrandt wrote:
I get two columns once every week which are your Value and Name columns.
That means in the second week I have four columns. In the second week, the
first column is a Value column and the second column is a Name column, both
of which are from the first week. The third column is a Value column and
the forth column is a Name column from the second week. In the second week,
I want to find out what Names appear in the 4th column and do not appear in
the 2nd column.
I will continuing adding two new columns every week. In the 4th week I will
have 8 columns and I will want to see how many Names appear in column 8 that
do not appear in column 2.
There will be other weeks when I want to compare a Name column to the Names
in column 2.
Copying and Pasting is working is now working with the data. I copy both
the Value and the Name for the Name that appears for the first time do a
Paste. This means I use the Name columns to see what names appear for the
first time and then select both the Value and Name columns and do a Copy >
Paste.
Can this be done with the data pilot? I am assuming that the data pilot
would be easier for me to learn than to start from scratch and learn Base.
All list processing software tools I'm aware of use the concept of
normalization:
http://support.microsoft.com/kb/283878/EN-US/
In short: List processing software works best with tables that grow by
rows with a constant amount of columns.
Spreadsheets have absolutely no concept of normalization and experienced
power users may derive all sorts of information from arbitrary table
layouts. This dark magic may be required when you have no control over
the incoming data layout (for instance data copied from a selection in a
browser window).
In this particular case we get controlled input data where the first
column always contains numbers and the second column contains only text.
All the built-in spreadsheet tools assume normalized lists, the data
pilot in particular.
For your problem I suggest that you collect all the weekly incoming data
in a database table together with an automatic time-stamp.
So your table looks like this:
Value Name Timestamp
13.5 | Walter | 2009-09-29 12:45
12.9 | Andreas | 2009-09-29 12:45
33.1 | Peter | 2009-09-29 12:45
3.5 | Walter | 2009-10-05 09:13
2.9 | Andreas | 2009-10-05 09:13
3.1 | Peter | 2009-10-05 09:13
You can derive all kinds of combinations from this vertically growing
list with week numbers and everything.
There is no reasonable limit of rows and you can easily compare sub-sets
with each other. In spreadsheets you can calculate with the same
database data.
I created this database table for you and you get the same when you
follow this:
- menu:File>New>Database... click the [Finish] button and store the new
database to some place where it can stay for a while (my private cash
register started in 2003).
- menu:Tools>SQL... paste the following command:
CREATE TABLE "Data"(
"Value" DECIMAL(10,2) NOT NULL,
"Name" VARCHAR(50) NOT NULL,
"Stamp" TIMESTAMP(0) DEFAULT CURRENT_TIME NOT NULL,
"ID" INTEGER GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL
PRIMARY KEY)
- push button [Execute]. "Command executed successfully" should be the
response.
- Close the command line tool
- menu:View>Refresh tables (tell Base that we modified the database
structure "behind it's back".
Now you see the table named "Data" we have generated with a simple
copy&paste.
Select it and call
- menu:Edit>Edit...
This reveals the inner structure of the table.
All fields are mandatory ("Allow empty values"=No in the graphical tool,
NOT NULL in the above SQL statement). They are not allowed to be empty.
The database will refuse to store rows with missing values.
Numeric field "Value" is a 10-digit decimal with 2 decimals behind the
dot ("Value" DECIMAL(10,2) NOT NULL). This is a commonly used type for
currencies.
Field "Name" is a case-sensitive text with a maximum lenght of 50
characters.
You may change the field types for your own two fields, but leave
"Stamp" and "ID".
"Stamp" gets the current time as default value for each newly created
row("Stamp" TIMESTAMP(0) DEFAULT CURRENT_TIME NOT NULL). One of the
worst limitations in Base is that this default value is not accessible
through the graphical user interface.
The ID column adds a unique ID number to each new row. It is required
for technical reasons (it's a "primary key"). Just ignore it.
#################
- Copy this:
SELECT YEAR( "Stamp" ) || '-' || WEEK( "Stamp" ) AS "Week", "Name" AS
"Client", "Value" AS "Amount", "Stamp" FROM "Data" ORDER BY "Stamp" ASC
Go to the queries section, create a new query in SQL view, paste the
command and save the query.
Then open the query and it should look like:
Week Client Amount Stamp
2009-41 Mary 23,4 05.10.09 21:08
2009-41 Paul 12,91 05.10.09 21:08
2009-41 Peter 33,11 05.10.09 21:08
A query can recombine a table in any order of rows and columns, using
any column names, sort order and filter criteria. It is possible to
combine data from more than one table.
##################
When you receive new spreadsheet data, all you've got to do is this
(about 5 seconds):
- Open the downloaded Excel file and the "beamer" (hit the F4 key).
- Expand your database, so you access the "Tables" and "Queries" sections.
- Select the data range including the column labels and drag the range
onto the "Tables" in the beamer's left pane.
- A wizard pops up where you type in the name of your table ("Data") and
choose option "Append Data". In the next step you can adjust the 2 sides
so the respective columns of the spreadsheet and database are side by
side. I arranged the table's column order so you don't have to adjust
anything.
When I drag the following 4 rows from a spreadsheet into table "Data",
"Amount" corresponds to database field "Value" and "Client" contains the
data for field "Name".
Amount Client
33,11 Peter
12,91 Paul
23,40 Mary
You can disregard the other two fields unless you want to import a
timestamp other than the current time (import last week's data). In this
case you fill a third spreadsheet column with the wanted date and import
3 columns to override the default time stamp.
######################
OK, I assume you have some data in your table and you see the table and
the query in the beamer window.
- Open a new spreadsheet.
- menu:Data>Data Pilot>Start...
- Create from data source and choose the name of your source and the
name of your query.
- Drag "Week" (calculated from "Stamp") into "Column Fields"
- Drag "Amount" ("Value" in the DB) into "Data Fields"
- Drag "Client" ("Name in the DB) into "Row Fields"
[More Options]
Specify A1 of a blank sheet as target.
Check all the other options. They don't hurt.
##########################
- Open the next Excel file
- Drag the list onto "Tables" and append data to table "Data"
- Refresh the pilot
We set up some definitions for a table a query and a data pilot once.
Then we can fill the defined table with new data by means of a simple
drag&drop operation once a week.
The suggested data pilot shows in a cross-table the amounts for every
(vertical) name by (horizontal) weeks.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
