Using ViewDBFWin

Contents
Command Line Arguments	Opening, Creating and Restructuring a Database
Adding New Records	Exclusive, Shared, Readonly and Restricted Access
Importing and Exporting Data Records	Deleting and Recalling Database Records
Deduplicating a Database	Changing a Record
Indexing	Filtering Reporting
Setting Fields	Statistics and Pivot Tables
Flags and Subsets	Macro Commands
SQL Statements	MJ Library Functions

Command Line Arguments

Syntax :-

ViewDBFWin [# or ~][database-file] [macro-file] [macro-defines] [-tXXX] [-pPriority] [+Field1] [+Field2]...

Optional database-file is the name of the database you want opened on loading the program. If this is preceded with the optional # sign, the database is opened in exclusive mode. If this is preceded with the optional ~ sign, the database is opened in shared mode. Otherwise it is opened in readonly mode. If it does not exist, you will be prompted to create it. If you use NUL for database-file, no database will be opened. If you prefix this NUL with a full path, then ViewDBFWin will start up in that directory. You can execute a macro with the second command line argument macro-file. If you would like to repeatedly run the macro, specify the optional -t switch followed by how ever many seconds are required between macro runs, for example, -t3600 means every hour. If you specify the -p switch, Priority can be Normal or High (eg. -pnormal or -phigh), and this runs ViewDBFWin at that priority level. It defaults to Idle priority so that ViewDBFWin can run in the background while you do other things on the PC. Set it higher if you need a macro to run more quickly, for example.

Optional macro-file is the name of the file containing macro commands you want executing on loading the program. You can also set up DEFINES using the macro-defines parameter. Simply specify the "name=expr" pairs on the command line to set up defines for the ViewDBFWin session. Enclose a pair in double quotes if it contains a space.

Specifying fieldnames prefixed with a plus sign (+Field1 ...) on the commandline, will force the program into Restricted mode. In this mode, only the fields specified can be changed (if you are also in Shared or Exclusive mode), and no further databases can be opened. All update functionality, save changing the specified fields, is disabled in this mode. This stops people making unwitting changes to sensitive data. For example to open database FISH.DBF and only be able to change the field SCALES, while setting up 2 defines for #start_date# and #start_time# with the date and time of program inception, the commandline would be :-

viewdbfwin ~fish start_date=date() start_time=time() +scales

Opening, Creating and Restructuring a Database

ViewDBFWin is currently configured to use either FoxPro/Clipper/XBase DBF/FPT/CDX or ADT/ADM/ADI proprietary type databases. To open a database, click on the button on the right of the database filename box, or use the menu File and pick option Open/Create Database File. If the file does not exist and you confirm the creation prompt, an empty database with the name you chose, and a structure of a single 20 character field called NAME, is created for you. You can also associate a DKapture Compiled File with the database, where the fieldnames are the question labels in the CPD, using the Associate CPD File option on the File menu.

To change the structure of the database, make sure you are in Exclusive access mode and press the Structure button, or use the Structure option on the Edit menu. The right-hand record display window then displays the current database structure. You can now edit and change field definitions as well as add new ones, or move fields around. The format for field definitions is very simple. Each line in the text display is a field definition. The format should be

field-name,C[haracter] or N[umeric] or M[emo] or D[ate] or L[ogical],field-width,number-decimals

Field names can only have A-Z and 0-9 and _ characters and should not be more than 10 characters wide. If you specify Memo, Date or Logical field types, then you do not need to enter the field-width or number-decimals parts. If you specify Character field type, then you must supply a field-width, but the number-decimals is not required. If you specify Numeric field type, then you must specify the lot. You can cut, copy and paste field definitions, as this is a text editor window. You can print them too. Definitions are case insensitive. An example database structure :-


LREF,CHARACTER,6

REQUESTED,D

REDEMTYPE,NUMERIC,2,0

STAGGER,NUMERIC,1,0

SETTLEMENT,D

QUOTE,NUMERIC,12,2

LSTPAYDATE,DATE

PREPARED,DATE

COMMENT,MEMO

CLOSEDWITH,LOGICAL

WHODONE,CHARACTER,30

DATEDONE,DATE

TIMEDONE,CHARACTER,8

REDSCREEN,MEMO

When you are satisfied with the structure in the text window, press the Tab key (or click outside of the window) and confirm the restructure prompt. You can then choose from 2 methods to do the restructure. The Fast method does not handle field type changes very well, but it is very quick at changing field widths, dropping fields, or shuffling fields around. The Slow method (despite being significantly slower than the Fast method) can even intelligently put character fields containing varying date formats, successfully into a date field, even if the source format changes from one record to the next. After restructure, the original database is renamed to database-name_old.dbf (or .adt), in case things go wrong. All index orders are recreated after a restructure.

There are actually very many field types, but only those listed above are backwards-compatible with DBase4 DBF file format, and hence acceptable to Clipper and Excel. However, the more adventurous user may want to use any of the following field types (declaration must include numbers if specified below) :-


DBF or ADT Fields :-

FIELD1,VARCHAR,10 - Like a MEMO field - contains variable length strings

FIELD2,DOUBLE,8 - A numeric field with general floating point capabilities and a range of
between 1.7*10^-308 and 1.7*10^308 - Can accept literals of the form 1.556e28 when typed
directly into the field in the grid, but in expressions,
use 1.556*10^28 instead.

FIELD3,INTEGER - An integer field with a range of -2^31 up to 2^31

FIELD4,BINARY - A field which can store any type of binary data - see
Changing Fields to see how to populate this type of field

FIELD5,IMAGE - A field which can store any type of image - see
Changing Fields to see how to populate this type of field

FIELD6,CICHAR,30 - Like a standard character field, except it is always treated as
case-insensitive


ADT Only Fields :-

FIELD7,MONEY,14,2 - Stores money amounts in a special format so rounding errors do not occur.
Also displays this field with a currency symbol in the database grid.

FIELD8,TIMESTAMP - Stores both date and time in a single field. Displays this field according
to your PC's date and time format settings, in the database grid.

FIELD9,AUTOINC - An integer field which is automatically incremented for each new record added
to the database. It maintains itself and never needs to be populated by the user. This can be
useful to ensure a unique field for use in, say, MS Access tables through the ADS ODBC driver.

For renaming, retyping, resizing and repositioning fields, you can use the Change Field Details option on the Edit menu, or press Ctrl+A. This will prompt you for a change definition string (the format for which is documented in the entry for the CHANGEFIELD macro). You can change the name, type, size and position of any number of fields in one go using this option. A backup is made of the database before the changes, and has the same name as the original, but with the .BAK extension appended to the DBF, CDX, and FPT files (or ADT, ADI, and ADM files). Examples for fields called CODEREF and TELEREF which are up to 25 characters :-


Change CODEREF field name to BARCODEREF :-

CODEREF,BARCODEREF,C,25

Change CODEREF field position to the first column :-

CODEREF,CODEREF,C,25,(1)

Change field name, position, type, and width in one go for both fields :-

CODEREF,BOOKREF,N,20,0,(3);TELEREF,TELESALES,C,90,(2)

You can set any field to any valid expression or literal constant using the Set button. This can useful when used in conjunction with the RECNO() function for setting up a new column in a database. For example, if you had just added a date field called LOGDATE to the current database and wanted to populate it with consecutive dates starting at 1st January 1980 for the first record, then 2nd January 1980 for the second, and so on, you could set field LOGDATE to the expression :-
CTOD('1/1/1980')+RECNO()-1
See Help on Expression Syntax for more details on expressions.

You can close the current database using the Close Database File option on the File menu. If you open another database, the one you had open is closed automatically.
Back to the Top

Adding New Records

To add a new record to the database, go to the last record using Ctrl+End, or pressing the relevant button in the database navigation bar, and press the down arrow key. You can also use the + button in the database navigation bar. An asterisk appears in the left margin of the database grid, to denote a new record. When you are happy with the record, you can post it to the database using the tick button in the database navigation bar. You can cancel the record by pressing the cross button in the database navigation bar.

You can duplicate a database record any number of times, using the Dup. Rec button, or the Duplicate Record option on the Record menu. Enter the number of times you want to duplicate the current record and press enter. You will be positioned on the first of the duplicated records. You can then use the RECNO() technique above to populate it, if desired.
Back to the Top

Exclusive, Shared, Readonly and Restricted Access Modes

On the file menu, you can set the access level for the databases you want to open. Exclusive mode means you have sole access to the database and other people will not be able to open it. In this mode, you can pack the database, restructure it, reindex it, or build new indexes. Shared mode means you need to be able to update the database, but without shutting other users out. This mode uses record locking to avoid contention issues on a network. At this locking level, you can still set fields on visible records, but you cannot pack, reindex, or build new indexes. In Readonly mode, you cannot update the data, but you can run reports, do statistical analysis and pivot tables, and most other functions that do not require any updates to data held in records. Restricted mode is controlled only by the presence of '+' sign prefixed fieldname(s) on the command line, and not from the File menu. In this mode, most operations are disabled, and the functionality available is much like Readonly mode. However, you can open the database in Shared or Exclusive mode, and specify which fields are editable using the command line arguments. In Restricted mode, you will not be able to open any other databases or change the access mode you're in. In this mode, a lot of the buttons and menu options are greyed out or not there.
Back to the Top

Importing and Exporting Data Records

ViewDBFWin will import from DKapture Data File, DKapture Sample File, CSV, SDF and DBF (or ADT) formats, and export to CSV (delimited with any delimiter), SSS and flat file (fixed width) formats.

Import from DKapture Data File (under the File Menu or press Ctrl-F2) creates a new database using the DKapture compiled and datafiles to concoct the new database. The database name is the same as the compiled file name, but with the .DBF (or .ADT) extension, and _DAT added to the name. If it existed before, you are prompted as whether to overwrite it. If you choose not to split multis, multi-type data is read in as comma-separated codes, with a comma at the beginning and end of the field to help analysis. If you choose to split multis, then multis are split into separate single- digit numeric fields for each code in the code-frame. The fields are named multi question label plus _ plus the code number. For example, splitting a multi called Q4b with 4 codes and a no answer code would yield fields Q4b_0, Q4b_1, Q4b_ 2, Q4b_3, and Q4b_4. All other fields are named with question label for the data they contain, up to the 10-character limit for field names. Field names may be renamed to ensure uniqueness in the overall database. After manipulation, data can be re-exported back to DKapture data file format, using the Export function.

Import from DKapture Sample File (under the File Menu or press Ctrl-F3) creates a new database using the DKapture sample file to concoct the new database. The database name is the same as the sample file name, but with the .DBF (or .ADT) extension, and _SPL added to the name. If it existed before, you are prompted as whether to overwrite it. All data is read in as written in the SPL file, except for the special flags on the end of each sample record, which are translated into 8-digit numeric fields holding interviewer number, outcome code, number of no answers, status code, and number of calls respectively. All sample fields are made 1 wider than specified in the sample file to allow for future expansion, and are all character fields.

Import from SDF imports data from a fixed-width field file (SDF) into the current database. The SDF file should have one record per line, and each line should consist of plain text in fixed width fields that correspond to the destination database field widths. If a line is shorter than expected for the current database record length, the remaining fields are left blank.

Import from DBF appends records from another database file to the current database as best it can. Works best if the source and destination databases have identical or similar structures.

Merge from DBF (under the File Menu or press Ctrl-F4) adds the fields in the specified merge database file to the current database (or replaces data in matching fields if the option to do so is used), and then populates them by searching for an input key expression in the current index ordering and filling in the extra fields from the matching record in the merge database. This essentially adds or merges the data from records in the merge database to the current database, using the current index ordering and the evaluated key expression to determine which record in the current database the fields are for. The key expression is evaluated in the merge database, not the current database. If the current database is in natural record order, matching is done by record number, so the first record in the merge database will add its fields to the first record in the current database, and so on. If there is no match, the fields from the merge database record are not added or merged to any record in the current database, and are ignored. If record or data mismatches occur, you are prompted as to whether you want to roll back the database to its original state before the merge was attempted. After you have specified the database from which records will be merged, you will be prompted as to whether you want to "Only Update Matching Fields". If you answer yes, the fieldnames from the merge database are matched against fieldnames in the main database, and where matches occur, data is overwritten with data from corresponding field in the merge database. If you answer no, the fields from the merge database are added to the structure of the main database with any duplicate fieldnames being renamed to avoid a clash, and the data from the merge database is added to these new fields.

Import from CSV imports data from a comma-separated variable file. There are options to form a new database and derive the database structure intelligently from the underlying data in the CSV file, or force the database structure to be character fields matching the widths from the underlying data in the CSV file, or to append to the current database, doing its best to match CSV data with the field type it is supposed to populate, or to back up the DBF before appending to it. Fields within records are delimited by commas and any double quotes enclosing text are not taken into the data, but can be used to wrap text in fields that contain commas. It will try to intelligently decipher date formats for date fields. If you choose the option to write to a new database, then after import, the current database will have the same name as the CSV file but with the .DBF (or .ADT) extension. If it existed before, it is overwritten. The new database structure has fields that are one character wider than the maximum width encountered in the CSV file data, for character and numeric data types. If you check the "CSV File has a Fieldname Header Row" option when creating a new database, the first line of the CSV file should contain a comma-separated row of field names to name the fields in the new database. If the supplied field names are longer than 10 characters, they are truncated. If, after truncation, duplicate field names exist, they have the last character changed to one of 2-9, A- Z until they become unique. Otherwise for new databases, the fields are called CHARx for character fields, DATEx for date fields, VALUEx for numeric fields, and LOGICALx for boolean fields, where x is an incrementing sequence number. MEMO fields are imported as character fields.

Export to CSV or flat file outputs records to a file in CSV or fixed width format. There are options to append to the destination file or to overwrite it, include a field name header row, change the field delimiter character, output to DKapture DOS data format , and to output dates in YYYYMMDD format. To output to a fixed width format file where the field widths for the database control the padding in the output file, then use nothing for the delimiter character (backspace over whatever is there - remember, the space character can be used as a delimiter!). When using the comma as a delimiter, if a field contains double quotes, they are converted to single quotes. Also, when using the comma as a delimiter, if a field contains commas, it is output with double quotes around it. When using comma as a delimiter or fixed width output (no delimiter), if any individual field contains new line characters, they are converted to ASCII 160 (looks like a space, but isn't!). To specify control characters for the delimiter, use Hex Character Code(s) prefixed with %. For example, use delimiter %09 for tab delimited and delimiter %0D%0A for CR-LF delimited. There is also a SSS option which will produce a version 2.0 SSS XML map file (file name is the same as the output file but with an extension .xml) as well as couching the multi-coded data in the correct format in the output CSV file.

Form DKapture Data File from Field (under the File Menu) creates a new DKapture data file from a multi-line field which stores the data in the same format as the field DATARECORD which stores interviews in between appointments in the CATI system. You can specify the type of data file using the file extension; if it's a database extension, it will produce the DKapture data in DBF format, otherwise in DAT format. You can choose which field in the current database is for the serial number, and which is for the data itself. More details are at the macro command reference for DKFLDTODATA.

Incremental DKapture Backups (under the Tools Menu) uses special backup files to perform incremental backups of DKapture files, including databases. Further information on this feature can be found at the macro command INCBACKUP. After running this interactively, if any errors occurred, they are displayed in a window, and you can open databases to investigate what went wrong, while this log is displayed.
Back to the Top

Deleting and Recalling Database Records

To completely wipe clean an existing open database, you can use the Erase All Records option on the Edit menu. This clears all records from the database and reindexes it. This is an irreversible operation. Please be certain that you can lose the data, before using this command.

When you delete a database record (using Ctrl+Delete in the database grid), the record is not physically removed from the database. It is marked for deletion. To physically drop deleted records from a database, you have to pack it. After any deletion, the Pack button (at the right of the window) becomes enabled, and pressing it, or selecting the Pack Database option on the Edit menu, will remove all deleted records.

If you are on a record marked for deletion in the database grid, a Recall Record button becomes enabled. This reinstates the record so that it would stay in the database after a pack operation. You can also use the Recall Record option in the Record menu to do this.

You can mark as deleted just the filtered records, using the button next to the Filter text box, or using the Delete Filtered Records option under the Filters & Flags menu. When it has gone through and marked the records that match the current filter for deletion, you will be prompted to "Pack the Database". This simply removes the records marked as deleted, permanently from the database.

You can also Limit Records so that you only have a certain number of records matching an index key or evaluated expression. First, you set the index order that is going to group the records appropriately, and then use the "Limit Records" option on the Edit menu. You can alter the expression used to gauge which group records are in, and specify the maximum number of records from each group allowed. All excess records are marked for deletion. You are prompted as to whether to pack the database after the operation is complete. For example, if you have an index on SURNAME, and you only want 7 records from each letter of the alphabet, your expression would need to be changed to LEFT(UPPER(SURNAME),1) from UPPER(SURNAME) to achieve this.

If you want to recall all deleted records in a database, for example, when you have deleted the wrong records by mistake, and you haven't packed the database yet, you can set the filter to the expression deleted(), activate it, and click the button next to the filter text box. This usually deletes records that match the filter, but when the filter expression is deleted(), you are prompted as to whether you want to recall the records. This technique will also work where the filter expression begins with DELETED() AND, since all of these records will be marked as deleted. See Help on Expression Syntax for more details on expressions. There is also a menu option on the Edit menu to Recall All Deleted Records.
Back to the Top

Deduplicating a Database

There are 2 ways to deduplicate a database. Full deduplication marks all records considered duplicates as deleted. Subsequent deduplication marks records that are considered duplicates of another record as deleted, that is, the original record these duplicated records match is kept, and the subsequent matched records are marked as deleted. Subsequent deduplication has the option to collate values into numeric fields on the record that is kept. For example, if you had a database of financial transactions, and you wanted to add up the amounts in the field VALUE for all transactions of the same narrative (held in the field NARR), then you would put the database into narrative order, using an index on, say, UPPER(NARR), deduplicate subsequent transactions and collate into the field VALUE.

The criteria for deciding a duplicate is based on a deduplication key and the index ordering for the database. For example, if you wanted to deduplicate records based on post code, you would put the database into post code order. Usually, the deduplication key and the index key are the same. But they can be different, for example, in our transaction database, we may want to collate account balances based on totals of the field VALUE for each change in loan reference (held in field LREF). We have an index on the loan reference in date (field DATE) order, as would be natural in a transaction database. Rather than create a new index on just LREF, we could use the index order we have, but specify the deduplication key as LREF. This saves us building an ultimately useless index order.
Back to the Top

Changing a Record

When you double-click a field in the database grid display (or press Enter on it), the grid enters edit mode, and the contents of that field on that record become editable. Once you have entered the changes required, press Enter again. Now the changes have still not been posted to the record. They are posted when you move to a different record, or you click the Post Edit tick mark button. To cancel any changes to this record, click the Cancel Edit cross button. Hover the mouse over these buttons to see hints describing what they do.

If the field you want to change is a Memo field (variable length text field stored in a .FPT or .ADM file), edit mode puts the contents of this field into a text edit window on the right of the grid. If the memo data is RTF format text, the attributes of the text are automatically converted to display correctly in this edit window. After changing the field contents, press Tab to save your changes and move off the record, or Escape to abandon them. If the memo data was RTF format text, it is automatically stored in this format for you.

If you double-click on a Date field, a pop-up calendar appears so you can change the date more easily. To get rid of the calendar and input manually, simply click outside of it.

If you single-click on an Image field in Shared or Exclusive mode, then you can change the image by clicking on the image, and using the clipboard to copy, cut or paste image data to or from the image.

If you double-click on an Image or Binary field, the contents of the field are written to a temporary file called VDBFWIMG.xxx where xxx is the most likely extension for the underlying data (worked out automatically). A file chooser (geared towards image previewing) appears, allowing you to choose another picture or file to store in this field, or simply launch or view the underlying image or data in the VDBFWIMG file. You can store any type of data in these types of field.
Back to the Top

Indexing and Order Maintenance

To create a new index order, click the Index button, or use the Edit Maintain Indices menu option. Enter the name for the index you want to create, and then a valid expression for the order you want the records to appear in. Please note that index keys must all be of equal length for each record in the database. This means using the PADR, SUBSTR, LEFT or RIGHT functions for string-based keys, unless you are using plain field names in the expression. If you use the TRIM function in your expression, then you must wrap it with a PADR, SUBSTR, LEFT or RIGHT function. See Help on Expression Syntax for more details on expressions. Memo fields are not allowed in any index expression, unless used as the field to search in the CONTAINS function.

You can also build an index on any field in the database, simply by clicking the column header for that field in the database grid, and confirming the create prompt.

To use an index order, click the Index button, or use the Edit Maintain Indices menu option. Double-click on the name of the index order you want to set. Hovering the mouse over the Index button reveals a hint as to the current index expression and any filter condition in the current index order. Single-clicking an index in this list displays the index expression and optional condition (for a filtering index, if there is one) in the status bar at the bottom of the window. You can delete an index by single-clicking it, and pressing the Delete key. To leave the Index list window, click on its title bar, or anywhere outside of the index list.

Having set the required database ordering, you can locate the nearest matching record for any given search string. Use the Find button (to the right of the index button) or the Find Record option on the Edit menu. Enter what you want to locate and press enter. If there is no matching record, "None Found" is displayed, otherwise you are moved to the nearest matching record. If the database is in natural record order, the Find function will prompt you for a record number to go to, instead of a search string.

With an index order set, you can copy just the records wholly or partially matching any given key string, using the Copy Index Subset option on the Filters and Flags menu. Enter the string you want to match with the current index expression, and the name of the database you want to create to contain the matching records. See this example.
Back to the Top

Filtering Database Records

To create a filter, simply enter a valid expression that evaluates to True or False (logical expression) into the Filter text box, and check the Filter check box. This will then filter the database so that only records for which the filter expression is true, will be visible in the database grid. To stop filtering the records, uncheck the Filter check box, and all database records will become visible in the grid. See Help on Expression Syntax for more details on expressions.

With a filter in place, you can copy these records to a new database with the same structure, using the Copy Records button or the Copy Filtered/All Records option under the Filters & Flags menu. This button will copy all visible records with or without a filter in place. The destination database file will be overwritten by this procedure.

You can also mark as deleted just the filtered records, using the button next to the Filter text box, or using the Delete Filtered Records option under the Filters & Flags menu. When it has gone through and marked the records that match the current filter for deletion, you will be prompted to "Pack the Database". This simply removes the records marked as deleted, permanently from the database. If only deleted records are visible, it will recall them instead.

When filtered, all operations on a database except deduplication, apply to just the visible records, rather than all the records. If you append records to the database, merge in records, or zap the records, any filter is turned off before the operation occurs.

You can also build filters using the filter-builder dialog (right-click the filter checkbox), or by right-clicking on cells in the grid and selecting the relevant menu option.
Back to the Top

Reporting on Database Records

Reports can be run using templates files to shape and direct the output. To run the data through a template, use the Run a Report Template option on the Tools menu. You can design a template using the Design option on the same menu. For more information on template syntax, see the corresponding RUNREPORT macro command.

When you use the Tools menu option Design/Edit a Report Template and choose to design an RTF report, you can use the slidable tab stops at the top of the designer screen to align header titles with the data to come underneath. The tab stops apply to the whole document, and all tab stops are set whenever you drag one of them. The designer also supports drag and drop of fields and mnemonics onto the design area, with intelligent placement of the expression delimiters (`s).
Back to the Top

Setting Fields

You can set a field to a literal value or the result of an expression for the currently visible records in the database, by using the Set button, or using the Set Field on Records option on the Edit menu. Before setting fields, the program ensures that the order of the records is set to natural record order, so that any change to an index expression field will not disturb the sequence of records whilst the set is being performed.

After selecting the field you want to set, either enter the expression to assign to the field or enter a literal value preceded by % sign. For example, to set NEWDATE to 3 weeks past OLDDATE, set NEWDATE to the expression OLDDATE+21. To set NEWDATE to 17th August 2001, set NEWDATE to %17/8/2001.

Image fields (type I) can be set to a string denoting the filename holding the image for this field. If you have sequential images named "C:\IMAGES\PICTUREn.JPG" where n corresponds to the record number in the database, then you could populate the image field DBIMAGE in the database by using :-

SET DBIMAGE='C:\IMAGES\PICTURE'+LTRIM(STR(RECNO()))+'.JPG'

Data Statistics and Pivot Tables

You can perform a statistical summary on the currently visible records, using the Calculate Statistics option on the Tools menu. You are prompted for an numerical expression to analyse and a weighting expression (default 1 meaning unweighted). These can be just the field names of the numeric columns you want to analyse and weight by, or something more complex, for example, the length of a character field, as in LEN(RTRIM(SURNAME)), or even a statistical analysis of a related database using the MJLOOKUP function. See Help on Expression Syntax for more details on expressions. You can choose whether to ignore deleted records or not. After analysis, a simple dialog box is displayed with the following information (explanations for each are here) :-

the weighted sum of the matching records

the count of the matching records

the weighted average of the matching records

the unweighted maximum of the matching records

the unweighted minimum of the matching records

the weighted standard deviation of the matching records

the weighted coefficient of skewness of the matching records

the weighted coefficient of kurtosis of the matching records

the standard error of the mean

Pivot Tables and Charting

A full description of the options used for pivot tables and associated charts can be found at the PIVOTTABLE Macro Command Reference Section.

Pivot tables are used to "break out" all unique occurrences of any particular data from the database. Pivot tables are available for up to 2 expressions from the database. Use the Pivot Table option under the Tools menu, or press Ctrl-T, or click the "Pivot Table" button. If you only want to generate a table for one expression, use "1" for the break expression or leave it blank, and this column will be ignored. If you enter MJ Library Functions in the side or break expressions, drill-down (double-clicking a cell in the pivot table) may occasionally take a bit longer to work.

Having entered your expressions, you can also enter a description for each, so that tables and associated charts will have more meaningful annotation, instead of possibly complex expressions labelling them. You can enter an expression for the cells of the table, or use "1" to simply count unique occurrences of the expressions you have entered. If you choose cell contents other than a simple count, you can choose whether the cells should contain the average of the cell contents expression for each cell, and how many decimal places the cells should be displayed with. You can also choose as to whether the table should omit records where the side or break expression evaluates to an empty string, using the "Exclude..." checkboxes. You can also choose whether to include deleted records, incorporate side variable percentages, and whether to include all DKapture codes or just the ones that have been coded when tabulating single or multi coded DKapture questions from a DKapture dbf data file. You can also override titling and filter descriptions for the table and any associated chart produced.

The program then generates a pivot table and presents it in a separate window. There are options in this window to sort the table by the values of the expressions along the top and down the left of the table, or by the totals at the right and along the bottom of the table. You can export the table to a CSV or HTML file and open it in the associated application. The output file is called database-name_pivotXXX.csv (or .htm) where XXX is the station number of the PC. If the database has an associated DKapture compiled file, and the side or break expressions correlate to questions from this compiled file, the table's rows and columns will be labelled appropriately from the answer texts, and exports will output the DKapture questions as part of the header. You can also print the visible table as it appears on screen, and save this image to a BMP, PNG or JPEG, and Export the table to HTML format (like the CSV option). There are options to do various charts based around the table contents, with up to 25 break elements for bar charts. These can be optionally included in exports to HTML and Excel. There is also an option to auto-refresh the table. This recalculates the table every 60 seconds (or however long you have set). This is useful when the database is open in shared mode and other people are currently changing it. After you have closed the table, you can bring it back by pressing the F12 function key, or using the Show Last Pivot Table option on the View menu.

The HTML Pivot Table Export Template File

The elements that are output when exports to HTML or Excel are done, are controlled by a template file called $$PIVTEMPLATE.HTM, which contains mnemonics for which entities are to be included. The file is written for you if it does not already exist. It also controls the "look" of the output, and can be tailored to your company style, if necessary. ViewDBFWin first searches the database directory for this file, and if it doesn't find it there, it looks for it in the same directory as the ViewDBFWin.exe executable file. If it still doesn't find it, it writes one out to the executable directory in the following format :-

<HTML>
<HEAD>
<TITLE>ViewDBFWin Pivot Tables</TITLE>
<STYLE TYPE="text/css">
A:link {text-decoration:none; color:black;}
A:visited {text-decoration:none; color:black;}
A:hover {text-decoration:underline; color:black; background-color:lightgreen;}
A:active {text-decoration:underline; color:black;}
</STYLE>
</HEAD>
<BODY BGCOLOR="#B6EDCC">
<H2>_TITLE_</H2>
<H3>_DATABASE_</H3>
<FONT SIZE=-2>_DATETIME_</FONT><BR>
<H4>_FILTER_</H4>
_SIDE_<BR>
_BREAK_<BR>
<B>_TABLEVARS_</B><BR><BR>
<TABLE CELLPADDING=1 CELLSPACING=2 BORDER=1 BGCOLOR="#C7F0FC">
_PIVTABLE_
</TABLE><BR>
_ROWCOLCOUNT_<BR><BR>
_CHART_
<HR>
&copy; <B><I>_COPYRIGHT_</I></B>
</BODY>
</HTML>

Mnemonics Allowed :-

_TITLE_ is the caption for the table

_DATABASE_ is the filename of the database this table is for

_DATETIME_ is the current date and time

_FILTER_ is the description of the filter imposed, or the filter expression itself

_SIDE_ is the side expression or the DKapture questionnaire question corresponding to the side variable

_BREAK_ is the break expression or the DKapture questionnaire question corresponding to the break variable

_TABLEVARS_ is the definition of the table in terms of the variables used and the cell contents

_PIVTABLE_ is the table itself

_ROWCOLCOUNT_ is the count of the number of rows and columns this table has

_CHART_ is the chart of the table contents

_COPYRIGHT_ is the copyright notice for the company the software is licensed to

_BACKBUTTON_ is an optional "Back" button to help with on-line analysis surveys

The chart titling reflects that specified in the template, so if you customise the template and drop the copyright notice, any chart attached to the table will also have the copyright notice dropped from its titling.
Back to the Top

Flags and Subsets

It is possible to select random subsets of records from a database. Optionally, you can have a character field called FLAGVDBFW on the database you want a random subset from, in order to keep track of subsets done in previous sessions. This is achieved by having the FLAGVDBFW field populated with the text in the flag string field, as each record is randomly selected. If using flag strings, then any record chosen that has this subset's flag string in its FLAGVDBFW field already, is deemed to have been selected in a previous session, and will not be selected for this subset. The flag string text is appended to the FLAGVDBFW field or overwrites it, depending on the setting of the Flag Field radio box (top right of window). When a record is chosen, its FLAGVDBFW field is first checked to see if it contains the flag text. For example, pulling sample from a big master database, and doing multiple subsets, we would not want the same record in 2 different subsets, so the FLAGVDBFW field is a way of saying, "Have we had this one before?". If the FLAGVDBFW field contains the flag string text, the record is not chosen for the subset database. If it doesn't, the record's FLAGVDBFW field is populated with the flag text, and the record is then output to the destination database. If you want to add a FLAGVDBFW field to the database, use the Add/Alter Flag Field button, or the menu option on the Filters and Flags menu, and enter the required field width.

To start the process, optionally enter the flag text in the Flag Field String edit box. Optionally, ensure the FLAGVDBFW field exists - create one if it doesn't. Click the Subset button (or use the Random Subset of Records option on the Filters and Flags menu), and enter a name for the destination database that will hold the random subset of records. Then enter the desired number of records. The process then visits records at random and, when using flags, if the FLAGVDBFW field doesn't contain the flag text, it is output to the destination database. If not using flags (flag string is blank, or source database does not contain a character field called FLAGVDBFW), the random record is output to the destination database irrespective of any flag string or FLAGVDBFW field contents. The process continues until either the desired number of records have been selected and output, or it has looped 20 times the number of records in the main database since the last successful hit. The destination database file becomes the main database.

Subset will only select records from those currently visible, and this becomes very useful when trying to select a certain amount of sample records from a set of postcode sectors, for example. You would define the filter expression as something like :-
','+UPPER(LEFT(POSTCODE,3))+',' $ ',HA7,HA2,HA3,HA5,'
and then do the subset process for the number of records you want from these sectors. See Help on Expression Syntax for more details on expressions.

An additional subset function exists which takes subset definitions from a file, and performs the subset process for each line in the file. It is called SSFF (Subset from File) and can be accessed by pressing the SSFF button (on the left of the Subset button), or from the Random Subsets from File option on the File menu. You will then be prompted for a text file from which the definitions will be read. Each line in the chosen file should be in the following format :-

SSFF Syntax and Parameter Explanation
{filter-expression},number-required,[+]flag-string,source-DB,[+]destination-DB[,{index-order-expression}]
Filter-expression is enclosed in curly brackets and must be a valid ViewDBFWin expression. See Help on Expression Syntax for more details on expressions. It is used to filter the master (source) database records before performing the subset operation. If you are not using flags for the subset, put a space between the commas for the flag-string, otherwise, the optional + sign in front of flag string denotes flag string append mode rather than overwrite. Source-DB is the master database from which records are selected. Destination- DB is the name of the database to which random subsets will be written. This file is truncated if the optional + sign is not specified, otherwise it has records appended to it. The optional index-order-expression argument can be used to order the master database and take the top number-required from it in that order, rather than random records. Again, it must be enclosed in curly brackets and must be a valid XBase expression. The last source-DB becomes the new master database.