Welcome to autumo enterprise products.
Download. Try. Build fast. Grow solid.

QTools

Documentation

Table of Contents

  1. Preamble
  2. System Requirements
  3. Software
    1. Languages
    2. Installation
  4. Define your Excel definition file
    1. Field Names
    2. Types
    3. Aliases
    4. Define a Custom Type
    5. Define a Custom Structure Type
  5. Create a new QTools Project
  6. Project Settings
  7. Different Sample Data Creation Modes - Why?
  8. Load your Definition
  9. Define a Test Data Factor
  10. Define your Input
    1. Modifying existing Sample Data
    2. Rename | Adjust QlikView Script Tabs
  11. Process your Project
    1. Output Artifacts
  12. General Settings




Preamble

Sometimes it's good to have everything documented and sometimes it is just right to get started right away and play around with the tool. For those who don't like to read manuals might just want to have a look at the example projects provided:

QTools project that shows usage of all test data field types.
QTools example project that shows associative test data generated for a CRM QlikView model.

You might also wonder why you should use Excel to define a QTools project definition. In short, it's simple to do so and you can combine your definition also as a documentation; have a look at the example projects above. Furthermore, don't forget that there are alternatives to Excel: http://www.openoffice.org.



System Requirements



Software

The QTools software will neither be Microsoft nor Apple certificated. QTools is 'Trust | Freedom certificated'. QTools is 100% secure and loyal; please download QTools only from this homepage. Some more information about application languages and software installation in the following chapters:



Languages

QTools can be executed in German and English language. The language can be chosen at any time in the application itself. If someone needs another language, don't hesitate to contact us.



Installation

Download and double-click QTools-x.y.z.jar - you need to have OpenJDK 1.8+ or Java 1.8+ installed to run it.

If you have initially and successfully started QTools, the installer starts: Please follow the instructions.



Define your Excel definition file

Your Excel definition file must contain one Excel spreadsheet for every table you want to script and possibly generate. You need to define at least a field name and a field type column:

NOTE: The field name and the field type header names can be individually chosen but must be the same for every spreadsheet.

Furthermore, you must define an alias column that will be used if you activate the rename routine within the project settings of QTools, otherwise no alias column is needed. Aliases are used for the field name in the GUI of a QlikView model, so no technical field names appear on the surface of the QlikView model.

Please make sure that for every definition sheet you create, you use the same position for every column. In truth, the first occurrence of one header field (field name, field type or alias) must be at the same position for every spreadsheet. But it is possible to have other columns between the header fields, to define the header field names by your own and to have spreadsheets before, between and after definition spreadsheets. More on that topic later in the project settings section.



Field Names

Field names represent the technical names in your data source (Excel, Database, etc.). Keep the names meaningful according to good database naming design. QTools does not insert QlikView 'QUALIFY' commands with the generated QlikView script; you can add them by your own if necessary.



Types

The following table shows all field types you can use for sample test data generation:

Type:Format:Example or Description:
Unique ID 1, 2, 3, 4, 5, 6, ...
Foreign ID → Autom. association with Unique ID's in other tables for same field name
ID 1, 2, 3, 4, 5, 6, ...
Fixed= ...Fixed=<value>E.g. <value> = 2014 → 2014
Formula= ...Formula=<formula>Simple math expression "+-*/()", e.g. <formula> = {fieldA} * 10 - {fieldB} / 0.5
Subst= ...Subst=<value>Field substitution, e.g. <value> = Hello {fieldNameFirstName} {fieldNameLastName} → Hello Kenny Loggins
<customType> Custom types can be added to project path "<project>/input/sampledata": Add files named "c_<customType>.txt", e.g. "c_mydata.txt" containing sample data entries line by line. To use your sample data the type in the table definition excel file must then specified by "mydata" analogical the corresponding part of the file name, e.g. "mydata" (case-insensitive).
<structureType>:<index> This works like <customType>-type, but in addition the sample data file "c_<structureType>.txt" can have more than one value per line seperated by a semicolon ";". The index refers to the position of the single value in a line starting from 1 until maximum 3. With this you can define this type 3 times per table: <structureType>:1, <structureType>:2, <structureType>:3. It's guaranteed, that only the values of a line are used for one generated record. This is often used if a certain group structure must be warranted.
!<type> Any field type can have a NOT operator "!" in front of the type name. This causes that the generated value is of type <type> but it is certainly not the value that is already given to a field with the same type <type> in a record. Make sure there's only one <type> - NOT types !<type> can be used as many as you like.
<%%>-<type> Leave values blank for a certain amount of percentage. E.g. "20-Color" → Fill values of type color and leave 20% of the values blank.
1, 0 1
Active, Deactivated Active
Address 1795 Railroad Park
Address Line 2 Suite #100808
Asset Category up to 20 Mio. USD
Asset Class Liabilities
Asset Type Property Trusts
Asset Subtype Derivatives
Birth Date 1957-10-22
Blank [Empty String]
Boolean true or false
Business Name Dixie Office supplies
Buy, Sell Sell
CC Number 4716624558453522
Character j
Characters Between [3, 10][min chars, max chars]xfarpbqzqa
Characters Length [7][max chars]hgvjjfh
City Holy Springs
Client Type Discrectionary
Color Aqua, Magenta, Violet, etc.
Country Switzerland
Country City:<index> Built-in structure type <country>:<city>, see type <structureType>:<index>
Company 3Com Corp, Ecolab Inc., Gateway Inc., ...
Credit Card VISA, MasterCard, ...
Currency CHF
Date [2013-11-28][date]2013-11-28
Dates [2013-10-31, 2013-11-30, 2013-12-31, ...][date, ...]2013-10-31, 2013-11-30, 2013-12-31, ... → Values will be alternated
Base Date [2013-11-28, 0, 30][base date,
min days offset, max days offset]
2013-12-06
Date Between [2013-11-28, 2013-12-28][min date, max date]2013-12-17
Date Time Between [2013-11-28, 2013-12-28][min date, max date]2013-12-17 23:59
Date Time US Between [2013-11-28, 2013-12-28][min date, max date]2013-12-17 11:59 pm
Domain g-stream.ch
Email Address keysthey@ma1lbox.org
Encrypt 31c0159e5a7eb03e0f3baeaf5bfb34a1e052cb9d
Gaussian [100, 20][mean, deviation]90.96183843938753
Gender Male, Female
GUID b35e194a-0319-bb29-cf64-a1ffb448ea9952b0b877
First Name Robert
Float 0.55
Float Between [1, 2][min number, max number]1.2
Frequency Never, Once, Seldom, Often, Daily, Weekly, Monthly, Yearly
Language Vietnamese
Language Code en
Last Name Guttierez
Latitude -23.53794755
Longitude 133.98874293
Hex Color #F19CBB, #856088, etc.
IBAN DK8387188644726815
Income Group Asset Management
Income Type Fees
Investment Strategy Growth
IP4 173.211.173.21
IP6 366d:507b:8cd3:709d:3ccc:19dd:354d:f8a9
ISBN 978-1-60309-2395
Mac D7:03:B9:66:4B:B4
Name Vicki Fields
Number -1000000000 to 1000000000
Number Between [0, 100][min number, max number]55
Number Text [8][digit size of number]84239765
Number Up To [1000][max number]672
Password djP12wp
Phone Number (833) 281-6188
Prefix Mrs
Product Ventolex, Zoomflex, Stantax, ...
Project Apollo, Genesis, Phoenix, Voyager, ...
Pseudo ISIN GU00VRFTIFQE
Race Arabs
Region Africa
Shirt Size XL
State Pennsylvania
State Code TX
Street Name Canneville
Street Suffix Street
Street Street Canneville
Suffix Phd
Swift KBSOCH2225G
Text Between [50, 100][min text length,
max text lenght]
came they shepherd trying sill they in in trying plane ball
Text Length [40][max text length]discovered captain demanded will wrong wrong
Time 23:59
Time US 11:59 pm
Time Zone Europe/Zurich
Transaction Subtype Interest
Transaction Type Sell
Username kbaldwin
Word ghost
Word Between [8, 12][min word length,
max word length]
television
Word Length [10][word length]constantly
ZIP 38620
- [Empty String]

NOTE: Type names are case-insensitive. You even can skip blank characters. Furthermore, in menu 'Info' chose menu item 'Browse Types for Excel Definition...' to browse and lookup all field types that can be used.


Within QTools you can lookup and browse all available types:



Aliases

Aliases are bound to the rename routine that can be configured in the project settings. They are only used whenever you want to have representable field names in your QlikView model.



Define a Custom Type

Custom types can be added to project path '<project>/input/sampledata': Add a file named 'c_<customType>.txt', e.g. 'c_mydata.txt' containing sample data entries line by line. To use your custom data the type in the table definition excel file must then be specified by 'mydata' analogical the corresponding part of the file name.

Custom Type Example:
File: "<project>/input/sampledata/c_myType.txt" → New type: myType
Activity
Campaign
Collection
Alert
Queue
Case
Announcement
Opportunity
Retail Segmentation
Corporate Segmentation

NOTE: The custom type names are case-insensitive.



Define a Custom Structure Type

Custom structure types are useful whenever you want to have multiple test data field values that belong together for one test data record generated. Basically, custom structure types are defined the same way simple custom types are defined, but you have multiple values separated by a semicolon ';' per line in your custom type file.

The index after the structured custom type name used in the table definition excel file and the colon ':' refers to the position of the single value in a value line starting from 1 until maximum 3. With this you can define this type 3 times per table: <structureType>:1, <structureType>:2, <structureType>:3. In this case the corresponding sample data file would be named 'c_<structureType.txt>'. It's guaranteed, that only the values of a line are used for one generated record. This is often used if a certain group structure must be preserved.

Custom Structure Type Example:
File: "<project>/input/sampledata/c_aCustomStrucType.txt" → New types: aCustomStrucType:1, aCustomStrucType:2, aCustomStrucType:3
Moneymarket;Cash CHF;Money Account
Moneymarket;Cash CHF;Cash Deposit
Moneymarket;Cash EUR;Money Account
Moneymarket;Cash EUR;Cash Deposit
Moneymarket;Cash USD;Money Account
Moneymarket;Cash USD;Cash Deposit
Bonds;Bonds Emerging Markets;Bond - Fixed Rate
Bonds;Bonds Emerging Markets;Fund - Bond
Bonds;Bonds High Yield;Bond - Note Fixed Rate
Bonds;Bonds High Yield;Bond - Note Floating Rate
Bonds;Bonds High Yield;Fund - other
Bonds;Bonds CHF;Bond - Fixed Rate
Bonds;Bonds CHF;Bond - Floating Rate
Bonds;Bonds CHF;Hybrid
Bonds;Bonds EUR;Bond - Fixed Rate
Bonds;Bonds EUR;Bond - Floating Rate
Bonds;Bonds EUR;Hybrid
Bonds;Bonds USD;Bond - Fixed Rate
Bonds;Bonds USD;Bond - Floating Rate
Bonds;Bonds USD;Hybrid
Equity;Equity Emerging;Fund - Equity
Equity;Equity Emerging;Share - Registered
Equity;Equity Emerging;Warrant
Equity;Equity Eurozone;Fund - Equity
Equity;Equity Eurozone;Share - Bearer
Equity;Equity Eurozone;Share - Preferred
Equity;Equity Eurozone;Share - Registered
Equity;Equity North America;Fund - Equity
Equity;Equity North America;Option - Share
Equity;Equity North America;Share - Bearer
Equity;Equity North America;Share - Preferred
Equity;Equity North America;Share - Registered
Equity;Equity North America;Warrant
Equity;Equity Switzerland;Dividend
Equity;Equity Switzerland;Fund - Equity
Equity;Equity Switzerland;Participation
Equity;Equity Switzerland;Share Bearer
Equity;Equity Switzerland;Share Registered


Create a new QTools Project

To create a new QTools project you chose 'New' from the Toolbar or chose menu item 'New Project...' from menu 'File', then enter a project name:


NOTE: New projects are created in the folder 'QToolsProjects' which resides in the user's folder. On Mac typically '/Users/<Username>/QToolsProjects' and on Windows 'C:\Users\<Username>\QToolsProjects'. If you want to change the project root folder chose menu item 'General Settings...' from menu 'Preferences' and enter your new project root folder.

The creation of the project creates some folders and files within the new project folder:



Project Settings

After the creation of the project the settings should be made:

  1. Chose Excel definition file
  2. Define offsets:
    1. Sheet offset → optional, define offset for first spreadsheet that contains a table definition.
    2. Column offset(X) → mandatory, define offset from left of the table definition spreadsheets for first occurrence one of the field header name defined in the column mapping.
    3. Row offset(Y) → mandatory, define offset from top of the table definition spreadsheets for first occurrence one of the field header name defined in the column mapping.
  3. Column mapping: Define your field header names for the columns containing field names, field types and aliases.
  4. Pre- and Post-Scripts: Add Pre- and Post-script if you want to have such sections in the generated QlikView script where you can previously add your own script parts.
  5. Features:
    1. Include rename routine → Adds the script part for aliases and generates an excel file where field names and aliases are mapped.
    2. Include Geo-data → Adds the script parts with countries, country flag image paths and geo locations (latitude, longitude) as well provides a zip file with the flag images.
    3. Include QV-toolbar → Adds the script part for customized QlikView toolbar and right-click context menu entries as well as the necessary QlikView extension and excel file for customizing the toolbar and the right-click context menu.
    4. Include translation → Adds the script part for QlikView model language translations and the excel file where the translations can be defined.
    5. Include variables → Adds the script part for external variables and the excel file where the external variables can be defined.
  6. Sample data:
    1. Create Sample Data → Check box if you want to generate associative (with foreign keys) sample test data. The defined 'Amount of Records' will be multiplied with the 'Test data factor' that is defined for every table loaded. See later chapters for test data factor definition.
    2. Copy Sample Data to input folder for modifications → If you choose this option, the internal QTools sample data files for built-in types are copied to the folder '<project>/input/sampledata'. From now on, this copied sample data files are used for built-in types. Hence, you can change and modify the whole spectrum of sample data that is used for test data generation.
    3. Output format → Choose desired output format: XLS, XLSX (10 times slower than XLS), CSV and SQL (MySql syntax). If 'Create Big Sample Data' or 'Create Big Associative Sample Data' option is chosen CSV and SQL format are supported only.


Different Sample Data Creation Modes - Why?

Obsolete: The are no different modes anymore. QTools handles capacity issues internally now (QTools version => 1.2.0).

QTools takes advantage of the best combination of system resources (memory versus disk IO) and output formats as well as results in the shortest execution runtime possible.



Load your Definition

When you have defined your Excel definition sheet you can load the definition into QTools without processing the project. To do so press the 'Load' button:

If your definition file is correct, you'll see an output within the console most likely in the upper image. If there is an inappropriate definition you'll receive warning and error messages that indicate the problem.

If you look at the tables section you see all tables loaded:



Define a Test Data Factor

The test data factor (see previous image) can be specified for every table loaded. It indicates the amount of test data generated for every table; e.g. 'Test data factor' = 4 and 'Amount of Records' = 1000 → 4 x 1000 → 4000 sample test data records will be generated for the corresponding table. The maximum value for the test data factor is 1000. Hence, you can generate up to 1000000 (1 million) associative records per table.

NOTE: If you have an error in your definition all tables already loaded will be removed from the project and hence the test data factor for every table defined too. So keep your definition correct and then define test data factors.


Define your Input

As earlier mentioned, you have great influence how the project output (QlikView script and test data) is generated. If you have chosen 'Copy Sample Data to input folder for custom modifications' (on settings page) you'll find a couple of files in the project's input folder '<project>/input/sampledata':



Modifying existing Sample Data

You can customize every sample data file the way you want. Just edit them in a text editor. The location '<project>/input/sampledata' is also the location where you place your custom type and structured custom type files.

E.g. modifying the built-in type 'BUSINESSTYPES.txt':

E.g. modifying the structured built-in type 'COUNTRYCITY.txt':



Rename | Adjust QlikView Script Tabs

Let's have a look at the file 'qt_qvSectionNames.txt':

By switching the flag 'createTabs' (true|false) you define if you want to have the generated QlikView script separated into tabs.

If you switch 'createTabs' to 'true', you can define the QlikView tab names by your own in the 2nd section of the file in each case after the '=' character.



Process your Project

If your Excel definition file is well formed, you have defined you input data, you have your settings set, you have defined your test data factors for every table and the desired features have been chosen it's time to process your QTools project by pressing the button 'Execute':

NOTE: The Excel definition file is always reloaded when you process your project.

After successful processing, the output script screen is activated:



Output Artifacts

Let's have a look a the project output folder '<project>/output/'. Here's an example of the maximum output (All formats: XLS, XLSX, CSV, SQL | All features selected) for the project 'My New QTools Project':

Description of the output files:



General Settings

Within the general settings you can change your project root path. This location will be used to create new QTools projects.


Contact | © autumo GmbH

Accept
This website is using cookies. More details