The lazy Qlik developer part 1: Ctrl-Q recipes
Ctrl-Q recipes for automating otherwise manual, repetitive, time consuming and error prone tasks when developing Qlik Sense applications.
Or: How to get more done with less work.
I admit it. I am lazy.
I really, really don't like doing repetitive tasks. They might be a necessary evil sometimes, but no - not something I appreciate.
This is also one of the main drivers behind all the various Butler tools as well as the new Ctrl-Q tool: Automate and simplify tasks that are otherwise hard to achieve at all, repetitive, error prone or just plain boring.
From time to time I get feedback that these tools are difficult to use, or at least difficult to get started with. Understood - I hear you.
Still, I honestly don't think it's that bad. I believe it's more a matter of these kind of tools being something new and somewhat unusual in the Qlik world. Concepts like DevOps, proper monitoring of Sense and automation is gaining traction in our community, but are still in most cases relatively rare and basic compared to the rest of the IT world.
So, I figured it could be useful with a series of blog posts that give concrete examples of how to use the various tools in the Butler/Ctrl-Q family.
Hopefully this will lower the barrier of entry and save a few hours getting-started-time for some of you out there.
First out is a set of ready-to-use recipes for Ctrl-Q.
Some things need to be adjusted to your (client-managed) Qlik Sense environment, for example host name/IP address of your server, what user to authenticate as etc. Once you have adjusted those, the various recipies should work out of the box.
The plan is to follow up with more posts on this theme (for other Butler tools/use cases) during coming weeks and months - feel free to share what topics would be of interest.
Ctrl-Q: automate high-value Qlik Sense workflows and tasks
The main Ctrl-Q page is found at github.com/ptarmiganlabs/ctrl-q.A few subsections of that page are of special interest:
- The official documentation is on the main page of the GitHub repository.
- Windows, macOS and Linux binaries can be downloaded from the releases page.
- Ideas for improvements? The Discussions page is the place to submit those.
- Found a bug? There's a dedicated page for that too. Please try to include as much info as possible about your specific situation. Especially which parameters and options you used to start Ctrl-Q.
What can Ctrl-Q do?
Ctrl-Q consists of a single application binary (Windows, macOS and Linux versions available) that include a set of commands that do different things.
Each command then has a set of options, some of which are common to all commands (e.g. authentication) - but some are unique to the command at hand.
Some options are optional and some are mandatory. Ok, maybe not the best lingo to have non-optional options.... but that's the way it is.If you forget to include a mandatory option Ctrl-Q will let you know.
Some options have default values, some don't. The command's help text will show what the default values are.
Let's look at what commands there are:
PS C:\tools\ctrl-q> .\ctrl-q.exe help
Usage: ctrl-q [options] [command]
Ctrl-Q is a command line utility for interacting with client-managed Qlik Sense Enterprise on Windows servers.
Among other things the tool does bulk import of apps and tasks, manipulates master items and scrambles in-app data.
Options:
-V, --version output the version number
-h, --help display help for command
Commands:
master-item-import [options] create master items based on definitions in a file on disk
master-item-measure-get [options] get info about one or more master measures
master-item-measure-delete [options] delete master measure(s)
master-item-dim-get [options] get info about one or more master dimensions
master-item-dim-delete [options] delete master dimension(s)
task-get [options] get info about one or more tasks
task-import [options] create tasks based on definitions in a file on disk, optionally also importing apps from QVF files.
app-import [options] import apps/upload QVF files on disk to Sense based on definitions in Excel file.
help [command] display help for command
Each command also has its own help page, accesible by adding "--help" to the command line. Let's look at the help for the "bookmark-get" command:
PS C:\tools\ctrl-q> .\ctrl-q.exe bookmark-get --help
Usage: ctrl-q bookmark-get [options]
get info about one or more bookmarks
Options:
--log-level <level> log level (choices: "error", "warn", "info", "verbose", "debug", "silly", default: "info")
--host <host> Qlik Sense server IP/FQDN
--port <port> Qlik Sense server engine port (default: "4747")
--schema-version <string> Qlik Sense engine schema version (default: "12.612.0")
--app-id <id> Qlik Sense app ID
--virtual-proxy <prefix> Qlik Sense virtual proxy prefix (default: "")
--secure <true|false> connection to Qlik Sense engine is via https (default: true)
--auth-user-dir <directory> user directory for user to connect with
--auth-user-id <userid> user ID for user to connect with
-a, --auth-type <type> authentication type (choices: "cert", default: "cert")
--auth-cert-file <file> Qlik Sense certificate file (exported from QMC) (default: "./cert/client.pem")
--auth-cert-key-file <file> Qlik Sense certificate key file (exported from QMC) (default: "./cert/client_key.pem")
--auth-root-cert-file <file> Qlik Sense root certificate file (exported from QMC) (default: "./cert/root.pem")
--id-type <type> type of bookmark identifier passed in the --bookmark option (choices: "id", "name", default: "name")
--bookmark <bookmarks...> bookmark to retrieve. If not specified all bookmarks will be retrieved
--output-format <json|table> output format (default: "json")
-h, --help display help for command
💡
NOTE: The examples/recipes below highlight a subset of the available commands.
Bulk import of apps and reload tasks
This command can be a real time saver when you have large amounts of tasks (and optionally apps) that should be created (and imported from QVF files).
An Excel file named "tasks.xlsx" contains two sheets. One defines what apps should be imported, one sheet defines what reload tasks should be created.
- The new reload tasks can be associated with already existing or new apps.
- Task chains can be constructed, referencing both existing and new reload tasks.
In this example 3 apps are imported and 4 tasks then created, including a short reload chain consisting of two of the just created apps and tasks.
PS C:\tools\ctrl-q> .\ctrl-q.exe task-import `
--auth-type cert `
--host 192.168.100.109 `
--auth-cert-file ./cert/client.pem `
--auth-cert-key-file ./cert/client_key.pem `
--auth-user-dir LAB `
--auth-user-id goran `
--file-type excel `
--file-name tasks.xlsx `
--sheet-name "Ctrl-Q task import 2" `
--import-app `
--import-app-sheet-name "App import"
2023-03-10T10:22:00.342Z info: -----------------------------------------------------------
2023-03-10T10:22:00.342Z info: | Ctrl-Q
2023-03-10T10:22:00.342Z info: |
2023-03-10T10:22:00.342Z info: | Version : 3.6.0
2023-03-10T10:22:00.342Z info: | Log level : info
2023-03-10T10:22:00.342Z info: |
2023-03-10T10:22:00.342Z info: | Command : task-import
2023-03-10T10:22:00.342Z info: | : create tasks based on definitions in a file on disk, optionally also importing apps from QVF files.
2023-03-10T10:22:00.342Z info: |
2023-03-10T10:22:00.342Z info: | Run Ctrl-Q with the '--help' option to see a list of all available options for this command.
2023-03-10T10:22:00.342Z info: |
2023-03-10T10:22:00.342Z info: | https://github.com/ptarmiganlabs/ctrl-q
2023-03-10T10:22:00.342Z info: ----------------------------------------------------------
2023-03-10T10:22:00.342Z info:
2023-03-10T10:22:00.342Z info: Import tasks from definitions in file "tasks.xlsx"
2023-03-10T10:22:00.388Z info: Importing app "App 1" from file "C:/tools/ctrl-q/testdata/App import 1.qvf"
2023-03-10T10:22:01.654Z info: Importing app "App 2" from file "C:/tools/ctrl-q/testdata/App import 2.qvf"
2023-03-10T10:22:03.132Z info: Importing app "App 3" from file "C:/tools/ctrl-q/testdata/App import 3.qvf"
2023-03-10T10:22:04.617Z info: CREATE RELOAD TASK IN QSEOW: "Manually triggered reload of Always failing reload (no delay)", new task id: aae41d06-dfd9-48e1-95f1-b51d5d8fd1bb. Result: 201/Created.
2023-03-10T10:22:04.741Z info: CREATE RELOAD TASK IN QSEOW: "Manually triggered reload of App1 🏆", new task id: 1f812ca4-e3a5-4a0c-babe-1496d38fc1b8. Result: 201/Created.
2023-03-10T10:22:04.804Z info: CREATE RELOAD TASK IN QSEOW: "Manually triggered reload of Butler 7 Slack debug", new task id: dbaec187-257c-48d8-a6ff-ed4495369db9. Result: 201/Created.
2023-03-10T10:22:04.945Z info: CREATE RELOAD TASK IN QSEOW: "Reload task of Lab 1_1", new task id: 9131985d-61b5-4ccf-89d6-8889022f11e0. Result: 201/Created.
2023-03-10T10:22:04.945Z info: -------------------------------------------------------------------
2023-03-10T10:22:04.945Z info: Creating composite events for the just created tasks...
2023-03-10T10:22:05.022Z info: CREATE COMPOSITE EVENT IN QSEOW: Event name="Trigger when upstream tasks are done" for task ID 9131985d-61b5-4ccf-89d6-8889022f11e0. Result: 201/Created.
View task tree
QlikView had this, but it has never arrived in the official Qlik Sense product. The command is quite flexible, by using the "--tree-details" option it's possible to control which task details are shown in the tree.
PS C:\tools\ctrl-q> .\ctrl-q.exe task-get `
--auth-type cert `
--host 192.168.100.109 `
--auth-user-dir LAB `
--auth-user-id goran `
--output-format tree `
--output-dest screen `
--tree-icons `
--text-color yes `
--tree-details nextstart appname
2023-03-10T10:26:20.531Z info: -----------------------------------------------------------
2023-03-10T10:26:20.531Z info: | Ctrl-Q
2023-03-10T10:26:20.531Z info: |
2023-03-10T10:26:20.531Z info: | Version : 3.6.0
2023-03-10T10:26:20.531Z info: | Log level : info
2023-03-10T10:26:20.531Z info: |
2023-03-10T10:26:20.531Z info: | Command : task-get
2023-03-10T10:26:20.531Z info: | : get info about one or more tasks
2023-03-10T10:26:20.531Z info: |
2023-03-10T10:26:20.531Z info: | Run Ctrl-Q with the '--help' option to see a list of all available options for this command.
2023-03-10T10:26:20.531Z info: |
2023-03-10T10:26:20.531Z info: | https://github.com/ptarmiganlabs/ctrl-q
2023-03-10T10:26:20.531Z info: ----------------------------------------------------------
2023-03-10T10:26:20.531Z info:
2023-03-10T10:26:21.749Z info: # rows in tree: 26
2023-03-10T10:26:21.749Z info:
├─ ⏰ --==| Scheduled tasks |==--
│ ├─ ✅ Reload task of Meetup.com, Next start: 2023-03-10T16:35:42.000Z, App name: Meetup.com
│ ├─ ❌ Manually triggered reload of Always failing reload (no delay), Next start: , App name: Always failing reload (no delay)
│ │ └─ ✅ Reload task of Lab 1_1, Next start: 2023-03-10T10:37:26.000Z, App name: Lab 1
│ ├─ ✅ Reload task of Reload monitor, Next start: , App name: Reload monitor
│ ├─ 🚫 Reload task of Training - Field indexing, Next start: , App name: Training - Field indexing
│ ├─ ✅ Reload task of App3 👻, Next start: 2023-03-11T15:01:31.000Z, App name: App3
│ ├─ 🛑 Reload of Test data - Seattle library checkouts & collection inventory, Next start: , App name: Test data - Seattle library checkouts & collection inventory
│ │ └─ ❌ Reload task of Test failing reloads (this app always fails reloading), Next start: , App name: Test failing reloads 1
│ ├─ ✅ Reload of Test data - Seattle checkouts by title3, Next start: 2023-03-10T23:49:10.000Z, App name: Test data - Seattle checkouts by title
│ │ └─ ✅ Reload task of Meetup.com, Next start: 2023-03-10T16:35:42.000Z, App name: Meetup.com
│ ├─ ❌ Reload task of Minecraft Metrics, Next start: , App name: Minecraft Metrics
│ ├─ ❌ Reload of Test failing reloads 1 (emojis supported! 🤪), Next start: , App name: Test failing reloads 1
│ ├─ ✅ Reload task of Test data - Kickstarter, Next start: 2023-03-16T04:08:14.000Z, App name: Kickstarter
│ │ └─ ✅ Reload task of Test data - NYC parking tickets, Next start: , App name: NYC parking tickets
│ │ ├─ 🛑 Reload of Test data - Seattle library checkouts & collection inventory, Next start: , App name: Test data - Seattle library checkouts & collection inventory
│ │ │ └─ ❌ Reload task of Test failing reloads (this app always fails reloading), Next start: , App name: Test failing reloads 1
│ │ └─ ✅ Reload task of Test data - NYT comments (1), Next start: , App name: NYT comments
│ │ └─ ✅ Reload of Test data - Seattle checkouts by title3, Next start: 2023-03-10T23:49:10.000Z, App name: Test data - Seattle checkouts by title
│ │ └─ ✅ Reload task of Meetup.com, Next start: 2023-03-10T16:35:42.000Z, App name: Meetup.com
Bulk update of task custom properties
The QMC does not (as of QSEoW 2022-Nov) support editing of several tasks at once. If you have to update custom properties of 200 reload tasks that will take a long time...
The following command does it in one go:
- The "Department" custom property will get two new values, "Finance" and "Sales".
- The new values are appended to any existing values in that custom property, rather than replacing them.
- Tasks with the specified IDs and any tasks having the "Butler 5.0 demo" tag will be updated.
- If the "Department" custom property is already set for a task there will be a warning. This can be overridden by specifying the "--overwrite" option.
PS C:\tools\ctrl-q> .\ctrl-q.exe task-custom-property-set `
--auth-type cert `
--host 192.168.100.109 `
--auth-cert-file ./cert/client.pem `
--auth-cert-key-file ./cert/client_key.pem `
--auth-user-dir LAB `
--auth-user-id goran `
--task-id 82bc3e66-c899-4e44-b52f-552145da5ee0 5520e710-91ad-41d2-aeb6-434cafbf366b `
--task-tag "Butler 5.0 demo" `
--custom-property-name Department `
--custom-property-value Finance Sales `
--update-mode append
2023-03-10T10:48:48.691Z info: -----------------------------------------------------------
2023-03-10T10:48:48.691Z info: | Ctrl-Q
2023-03-10T10:48:48.691Z info: |
2023-03-10T10:48:48.691Z info: | Version : 3.6.0
2023-03-10T10:48:48.691Z info: | Log level : info
2023-03-10T10:48:48.691Z info: |
2023-03-10T10:48:48.691Z info: | Command : task-custom-property-set
2023-03-10T10:48:48.691Z info: | : update a custom property of one or more tasks
2023-03-10T10:48:48.691Z info: |
2023-03-10T10:48:48.691Z info: | Run Ctrl-Q with the '--help' option to see a list of all available options for this command.
2023-03-10T10:48:48.691Z info: |
2023-03-10T10:48:48.691Z info: | https://github.com/ptarmiganlabs/ctrl-q
2023-03-10T10:48:48.691Z info: ----------------------------------------------------------
2023-03-10T10:48:48.691Z info:
2023-03-10T10:48:48.879Z info: Number of tasks that will be updated: 9
2023-03-10T10:48:48.879Z info:
2023-03-10T10:48:48.879Z info: -----------------------------------------------------------
2023-03-10T10:48:48.879Z info: Processing task "Manually triggered reload of App1 🏆" with ID=1f812ca4-e3a5-4a0c-babe-1496d38fc1b8
2023-03-10T10:48:48.879Z info: Starting updating custom property "Department" of task "Manually triggered reload of App1 🏆" with ID=1f812ca4-e3a5-4a0c-babe-1496d38fc1b8
2023-03-10T10:48:51.144Z info: ...Custom property "Department" on task "Manually triggered reload of App1 🏆" successfully updated.
2023-03-10T10:48:51.144Z info:
2023-03-10T10:48:51.144Z info: -----------------------------------------------------------
2023-03-10T10:48:51.144Z info: Processing task "Reload task of Test data - NYC parking tickets" with ID=09b3c78f-04dd-45e3-a4bf-1b074d6572fa
2023-03-10T10:48:51.144Z info: Starting updating custom property "Department" of task "Reload task of Test data - NYC parking tickets" with ID=09b3c78f-04dd-45e3-a4bf-1b074d6572fa
2023-03-10T10:48:51.144Z info:
Append new values to custom property "Department"? (y/n) y
2023-03-10T10:48:52.254Z info:
2023-03-10T10:48:52.364Z info: ...Custom property "Department" on task "Reload task of Test data - NYC parking tickets" successfully updated.
2023-03-10T10:48:52.364Z info:
2023-03-10T10:48:52.364Z info: -----------------------------------------------------------
2023-03-10T10:48:52.364Z info: Processing task "Reload of Test data - Seattle checkouts by title3" with ID=82bc3e66-c899-4e44-b52f-552145da5ee0
2023-03-10T10:48:52.364Z info: Starting updating custom property "Department" of task "Reload of Test data - Seattle checkouts by title3" with ID=82bc3e66-c899-4e44-b52f-552145da5ee0
2023-03-10T10:48:53.488Z info: ...Custom property "Department" on task "Reload of Test data - Seattle checkouts by title3" successfully updated.
2023-03-10T10:48:53.488Z info:
...
List master dimensions
It can be quite time consuming to verify that all master dimensions in a Sense app are correctly defined. By showing them in a table such verification becomes much easier.
In this example the app has 4 master dimensions.
PS C:\tools\ctrl-q> .\ctrl-q.exe master-item-dim-get `
--host 192.168.100.109 `
--app-id a3e0f5d2-000a-464f-998d-33d333b175d7 `
--output-format table `
--auth-user-dir LAB `
--auth-user-id goran
2023-03-10T10:54:41.977Z info: -----------------------------------------------------------
2023-03-10T10:54:41.992Z info: | Ctrl-Q
2023-03-10T10:54:41.992Z info: |
2023-03-10T10:54:41.992Z info: | Version : 3.6.0
2023-03-10T10:54:41.992Z info: | Log level : info
2023-03-10T10:54:41.992Z info: |
2023-03-10T10:54:41.992Z info: | Command : master-item-dim-get
2023-03-10T10:54:41.992Z info: | : get info about one or more master dimensions
2023-03-10T10:54:41.992Z info: |
2023-03-10T10:54:41.992Z info: | Run Ctrl-Q with the '--help' option to see a list of all available options for this command.
2023-03-10T10:54:41.992Z info: |
2023-03-10T10:54:41.992Z info: | https://github.com/ptarmiganlabs/ctrl-q
2023-03-10T10:54:41.992Z info: ----------------------------------------------------------
2023-03-10T10:54:41.992Z info:
2023-03-10T10:54:41.992Z info: Get master dimensions
2023-03-10T10:54:42.736Z info:
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Dimensions (4 dimension(s) found in the app)
│
├──────────────────────────────────────┬───────────┬─────────────────┬────────────────────────────────┬───────────────────────────────┬────────────────────────┬──────────────────┬──────────────────┬─────────────┬───────────────────────────────────────────────────────────────────────────────┬──────────┬──────────┬───────────┬──────────────────────────┬──────────────────────────┬──────────────────────────┬───────────┬────────────────┤
│ Id │ Type │ Title │ Description (static) │ Description (from expression) │ Description expression │ Label expression │ Definition count │ Definition │ Coloring │ Grouping │ Approved │ Published │ Publish time │ Created date │ Modified date │ Owner │ Tags │
├──────────────────────────────────────┼───────────┼─────────────────┼────────────────────────────────┼───────────────────────────────┼────────────────────────┼──────────────────┼──────────────────┼─────────────┼───────────────────────────────────────────────────────────────────────────────┼──────────┼──────────┼───────────┼──────────────────────────┼──────────────────────────┼──────────────────────────┼───────────┼────────────────┤
│ JDWuPK │ dimension │ Dimension 2-3-1 │ Description for 2-3-1 │ │ │ │ 3 │ Dim2 │ {"changeHash":"0.5399463179200534","baseColor":{"color":"#ffffff","index":1}} │ H │ false │ false │ 1753-01-01T00:00:00.000Z │ 2021-06-07T02:31:02.093Z │ 2021-06-07T02:31:02.093Z │ LAB\goran │ My awesome tag │
│ │ │ │ │ │ │ │ │ Dim3 │ │ │ │ │ │ │ │ │ │
│ │ │ │ │ │ │ │ │ Dim1 │ │ │ │ │ │ │ │ │ │
├──────────────────────────────────────┼───────────┼─────────────────┼────────────────────────────────┼───────────────────────────────┼────────────────────────┼──────────────────┼──────────────────┼─────────────┼───────────────────────────────────────────────────────────────────────────────┼──────────┼──────────┼───────────┼──────────────────────────┼──────────────────────────┼──────────────────────────┼───────────┼────────────────┤
│ b99e54d3-5177-419a-8573-7423175ba6ed │ dimension │ Country │ Country where a unit was sold. │ │ │ ='Country' │ 1 │ Country │ undefined │ N │ false │ false │ 1753-01-01T00:00:00.000Z │ 2023-02-22T15:34:24.760Z │ 2023-02-22T20:16:29.292Z │ LAB\goran │ Geo │
├──────────────────────────────────────┼───────────┼─────────────────┼────────────────────────────────┼───────────────────────────────┼────────────────────────┼──────────────────┼──────────────────┼─────────────┼───────────────────────────────────────────────────────────────────────────────┼──────────┼──────────┼───────────┼──────────────────────────┼──────────────────────────┼──────────────────────────┼───────────┼────────────────┤
│ 3047e493-f24f-4d31-a0fb-6b797874b049 │ dimension │ Salesperson │ The person who sold the unit. │ │ │ ='Salesperson' │ 1 │ Salesperson │ undefined │ N │ false │ false │ 1753-01-01T00:00:00.000Z │ 2023-03-08T16:45:26.610Z │ 2023-03-08T16:46:09.395Z │ LAB\goran │ Staff, Sales │
├──────────────────────────────────────┼───────────┼─────────────────┼────────────────────────────────┼───────────────────────────────┼────────────────────────┼──────────────────┼──────────────────┼─────────────┼───────────────────────────────────────────────────────────────────────────────┼──────────┼──────────┼───────────┼──────────────────────────┼──────────────────────────┼──────────────────────────┼───────────┼────────────────┤
│ c1364941-df5c-4c45-bbe5-8be9f8398373 │ dimension │ Sales month │ Date in which a unit was sold. │ │ │ ='Sales month' │ 1 │ Month_Sales │ undefined │ N │ false │ false │ 1753-01-01T00:00:00.000Z │ 2023-03-08T16:45:26.610Z │ 2023-03-08T16:46:09.395Z │ LAB\goran │ Sales calendar │
└──────────────────────────────────────┴───────────┴─────────────────┴────────────────────────────────┴───────────────────────────────┴────────────────────────┴──────────────────┴──────────────────┴─────────────┴───────────────────────────────────────────────────────────────────────────────┴──────────┴──────────┴───────────┴──────────────────────────┴──────────────────────────┴──────────────────────────┴───────────┴────────────────┘
A similar command exists for showing master measures in a Sense app.
Bulk creation of master items
Creating master items is traditionally done in the Qlik Sense editor. That's fine for a small number of master items, but for more complex scenarios with many master dimensions and measures this manual process can become error prone.
The recipe below imports master dimensions and measures from an Excel file into a specific Sense app.
- The Excel file is named "ctrl-q-master-items.xlsx" and has a sheet named "Sales", in which the master item definitions are found.
- Columns in the Excel sheet are referenced by name. The various "--col-..." options tell Ctrl-Q which column contains what information.
- Error checking is done to ensure only valid data is read from the Excel file. In this example one line in the Excel sheet contained an invalid master measure definition.
- In this example the master items already existed, meaning that they were updated rather than created.
PS C:\tools\ctrl-q> .\ctrl-q.exe master-item-import `
--host 192.168.100.109 `
--app-id a3e0f5d2-000a-464f-998d-33d333b175d7 `
--auth-user-dir LAB `
--auth-user-id goran `
--auth-type cert `
--file-type excel `
--file ./ctrl-q-master-items.xlsx `
--sheet Sales `
--col-ref-by name `
--col-item-type "Master item type" `
--col-master-item-name "Master Item Name" `
--col-master-item-descr Description `
--col-master-item-label Label `
--col-master-item-expr Expression `
--col-master-item-tag Tag
2023-03-10T11:06:42.085Z info: -----------------------------------------------------------
2023-03-10T11:06:42.085Z info: | Ctrl-Q
2023-03-10T11:06:42.085Z info: |
2023-03-10T11:06:42.085Z info: | Version : 3.6.0
2023-03-10T11:06:42.085Z info: | Log level : info
2023-03-10T11:06:42.085Z info: |
2023-03-10T11:06:42.085Z info: | Command : master-item-import
2023-03-10T11:06:42.085Z info: | : create master items based on definitions in a file on disk
2023-03-10T11:06:42.085Z info: |
2023-03-10T11:06:42.085Z info: | Run Ctrl-Q with the '--help' option to see a list of all available options for this command.
2023-03-10T11:06:42.085Z info: |
2023-03-10T11:06:42.085Z info: | https://github.com/ptarmiganlabs/ctrl-q
2023-03-10T11:06:42.085Z info: ----------------------------------------------------------
2023-03-10T11:06:42.085Z info:
2023-03-10T11:06:42.101Z info: Import master items from definitions in Excel file "./ctrl-q-master-items.xlsx"
2023-03-10T11:06:42.538Z info: Updated existing measure "No. of sold units"
2023-03-10T11:06:42.538Z info: Updated existing measure "No. of sold units (LY)"
2023-03-10T11:06:42.538Z info: Updated existing measure "Revenue EUR"
2023-03-10T11:06:42.554Z info: Updated existing measure "Revenue EUR (LY)"
2023-03-10T11:06:42.554Z info: Updated existing measure "Profit EUR"
2023-03-10T11:06:42.554Z warn: Found an unknown master item type: "measur". Ignoring this line in the imported file.
2023-03-10T11:06:42.554Z info: Updated existing measure "Profit EUR (LY)"
2023-03-10T11:06:42.554Z info: Updated existing dimension "Country"
2023-03-10T11:06:42.570Z info: Updated existing dimension "Sales month"
2023-03-10T11:06:42.570Z info: Updated existing dimension "Salesperson"
Scrambling in-app fields
Sometimes there is a need to scramble data in an app before it is shared with others. This feature was available in QlikView but is nowhere to be seen in Qlik Sense.
But it still exists - it's just that it isn't exposed in any user interface anywhere.
Ctrl-Q let you use this feature from the command line instead.
- The app with the specified app ID will be used as source app.
- The fields "Expression1", "Dim1" and "AsciiAlpha" will be scrambled
- A new app named "__ScrambledTest1" will be created, containing the scrambled data.
PS C:\tools\ctrl-q> .\ctrl-q.exe field-scramble `
--host 192.168.100.109 `
--app-id a3e0f5d2-000a-464f-998d-33d333b175d7 `
--auth-user-dir LAB `
--auth-user-id goran `
--field-name Expression1 Dim1 AsciiAlpha `
--new-app-name __ScrambledTest1
2023-03-10T11:12:20.698Z info: -----------------------------------------------------------
2023-03-10T11:12:20.698Z info: | Ctrl-Q
2023-03-10T11:12:20.698Z info: |
2023-03-10T11:12:20.698Z info: | Version : 3.6.0
2023-03-10T11:12:20.698Z info: | Log level : info
2023-03-10T11:12:20.698Z info: |
2023-03-10T11:12:20.698Z info: | Command : field-scramble
2023-03-10T11:12:20.698Z info: | : scramble one or more fields in an app. A new app with the scrambled data is created.
2023-03-10T11:12:20.698Z info: |
2023-03-10T11:12:20.698Z info: | Run Ctrl-Q with the '--help' option to see a list of all available options for this command.
2023-03-10T11:12:20.698Z info: |
2023-03-10T11:12:20.698Z info: | https://github.com/ptarmiganlabs/ctrl-q
2023-03-10T11:12:20.698Z info: ----------------------------------------------------------
2023-03-10T11:12:20.698Z info:
2023-03-10T11:12:20.713Z info: Scramble field
2023-03-10T11:12:21.135Z info: Scrambled field "Expression1"
2023-03-10T11:12:21.151Z info: Scrambled field "Dim1"
2023-03-10T11:12:21.151Z info: Scrambled field "AsciiAlpha"
2023-03-10T11:12:21.635Z info: Scrambled data written to new app "__ScrambledTest1" with app ID: 0b122466-2a5f-4b0b-8ad2-a3b341826490
List bookmarks
In-app bookmarks are useful but can become a pain in apps used by many people.
How many bookmarks are there? What do they refer to?
This recipe shows what bookmarks there are for a certain app.
In this case there is a single bookmark:
PS C:\tools\ctrl-q> .\ctrl-q.exe bookmark-get `
--host 192.168.100.109 `
--app-id a3e0f5d2-000a-464f-998d-33d333b175d7 `
--output-format table `
--auth-user-dir LAB `
--auth-user-id goran
2023-03-10T11:15:02.217Z info: -----------------------------------------------------------
2023-03-10T11:15:02.217Z info: | Ctrl-Q
2023-03-10T11:15:02.217Z info: |
2023-03-10T11:15:02.217Z info: | Version : 3.6.0
2023-03-10T11:15:02.217Z info: | Log level : info
2023-03-10T11:15:02.217Z info: |
2023-03-10T11:15:02.217Z info: | Command : bookmark-get
2023-03-10T11:15:02.217Z info: | : get info about one or more bookmarks
2023-03-10T11:15:02.217Z info: |
2023-03-10T11:15:02.217Z info: | Run Ctrl-Q with the '--help' option to see a list of all available options for this command.
2023-03-10T11:15:02.217Z info: |
2023-03-10T11:15:02.217Z info: | https://github.com/ptarmiganlabs/ctrl-q
2023-03-10T11:15:02.217Z info: ----------------------------------------------------------
2023-03-10T11:15:02.217Z info:
2023-03-10T11:15:02.233Z info: Get bookmarks
2023-03-10T11:15:02.592Z info: Bookmarks
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Bookmarks (1 bookmark(s) found in the app)
│
├──────────────────────────────────────┬──────────┬───────────┬─────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────┬──────────┬───────────┬──────────────────────────┬──────────────────────────┬──────────────────────────┬───────────┤
│ Id │ Type │ Title │ Description │ Bookmark definition │ Approved │ Published │ Publish time │ Created date │ Modified date │ Owner │
├──────────────────────────────────────┼──────────┼───────────┼─────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────┼──────────┼───────────┼──────────────────────────┼──────────────────────────┼──────────────────────────┼───────────┤
│ 81ec0c0d-c90c-431b-8c19-eff4048de404 │ bookmark │ Bookmark1 │ BM1 description │ {"qStateData":[{"qStateName":"$","qFieldItems":[{"qDef":{"qName":"Dim1","qType":"PRESENT"},"qSelectI │ false │ false │ 1753-01-01T00:00:00.000Z │ 2021-07-06T15:09:38.565Z │ 2021-07-06T15:09:38.565Z │ LAB\goran │
│ │ │ │ │ nfo":{"qRangeLo":"NaN","qRangeHi":"NaN","qNumberFormat":{"qType":"U","qnDec":10,"qUseThou":0},"qRang │ │ │ │ │ │ │
│ │ │ │ │ eInfo":[],"qContinuousRangeInfo":[]},"qValues":[],"qExcludedValues":[]}]}],"qUtcModifyTime":44383.71 │ │ │ │ │ │ │
│ │ │ │ │ 498842593,"qVariableItems":[],"qPatches":[]} │ │ │ │ │ │ │
└──────────────────────────────────────┴──────────┴───────────┴─────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────┴──────────┴───────────┴──────────────────────────┴──────────────────────────┴──────────────────────────┴───────────┘
Get load script
Especially for the Extract and Transform stages of a Qlik Sense task chain the value of the apps is in their scripts.
For this reason some companies are interested in automatically extracting the load script of apps and storing this as text files. A backup of sorts.
This recipe extracts the load script of a Sense app. Combine it with some PowerShell and maybe Qlik CLI, and it's possible to extract load scripts for well defined groups of apps.
PS C:\tools\ctrl-q> .\ctrl-q.exe script-get `
>> --host 192.168.100.109 `
>> --app-id a3e0f5d2-000a-464f-998d-33d333b175d7 `
>> --auth-user-dir LAB `
>> --auth-user-id goran
2023-03-10T11:19:47.907Z info: -----------------------------------------------------------
2023-03-10T11:19:47.907Z info: | Ctrl-Q
2023-03-10T11:19:47.907Z info: |
2023-03-10T11:19:47.907Z info: | Version : 3.6.0
2023-03-10T11:19:47.907Z info: | Log level : info
2023-03-10T11:19:47.907Z info: |
2023-03-10T11:19:47.907Z info: | Command : script-get
2023-03-10T11:19:47.907Z info: | : get script from Qlik Sense app
2023-03-10T11:19:47.907Z info: |
2023-03-10T11:19:47.907Z info: | Run Ctrl-Q with the '--help' option to see a list of all available options for this command.
2023-03-10T11:19:47.907Z info: |
2023-03-10T11:19:47.907Z info: | https://github.com/ptarmiganlabs/ctrl-q
2023-03-10T11:19:47.907Z info: ----------------------------------------------------------
2023-03-10T11:19:47.907Z info:
2023-03-10T11:19:48.236Z info: ----- Script metadata -----
2023-03-10T11:19:48.251Z info: App id: a3e0f5d2-000a-464f-998d-33d333b175d7
2023-03-10T11:19:48.251Z info: Created date: 2021-06-03T22:04:52.283Z
2023-03-10T11:19:48.251Z info: Modified date: 2021-06-04T15:42:23.759Z
2023-03-10T11:19:48.251Z info: ----- End script metadata -----
2023-03-10T11:19:48.251Z info:
///$tab Main
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='M/D/YYYY';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
SET FirstWeekDay=6;
SET BrokenWeeks=1;
SET ReferenceDay=0;
SET FirstMonthOfYear=1;
SET CollationLocale='en-US';
SET CreateSearchIndexOnReload=1;
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET LongMonthNames='January;February;March;April;May;June;July;August;September;October;November;December';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
SET LongDayNames='Monday;Tuesday;Wednesday;Thursday;Friday;Saturday;Sunday';
SET NumericalAbbreviation='3:k;6:M;9:G;12:T;15:P;18:E;21:Z;24:Y;-3:m;-6:μ;-9:n;-12:p;-15:f;-18:a;-21:z;-24:y';
Characters:
Load Chr(RecNo()+Ord('A')-1) as Alpha, RecNo() as Num autogenerate 26;
ASCII:
Load
if(RecNo()>=65 and RecNo()<=90,RecNo()-64) as Num,
Chr(RecNo()) as AsciiAlpha,
RecNo() as AsciiNum
autogenerate 255
Where (RecNo()>=32 and RecNo()<=126) or RecNo()>=160 ;
Transactions:
Load
TransLineID,
TransID,
mod(TransID,26)+1 as Num,
Pick(Ceil(3*Rand1),'A','B','C') as Dim1,
Pick(Ceil(6*Rand1),'a','b','c','d','e','f') as Dim2,
Pick(Ceil(3*Rand()),'X','Y','Z') as Dim3,
Round(1000*Rand()*Rand()*Rand1) as Expression1,
Round( 10*Rand()*Rand()*Rand1) as Expression2,
Round(Rand()*Rand1,0.00001) as Expression3;
Load
Rand() as Rand1,
IterNo() as TransLineID,
RecNo() as TransID
Autogenerate 1000
While Rand()<=0.5 or IterNo()=1;
Comment Field Dim1 With "This is a field comment";