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:
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.
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:
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.
Download the macOS, Linux or Windows package and execute it - you might have to outsmart Microsoft's Defender and Apple's Gatekeeper.
If you have initially and successfully started QTools, the installer starts: Please follow the instructions.
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 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.
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 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.
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.
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 |
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 macOS or Linux 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:
After the creation of the project the settings should be made:
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.
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:
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 in the licensed pro- and 1 in the free standard-version. Hence, you can generate up to 1'000'000 (1 million) associative records per table in the licensed pro- and 1000 in the free standard-version.
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.
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':
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':
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.
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:
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:
Within the general settings you can change your project root path. This location will be used to create new QTools projects.
