Qlik Sense Repository Explorer (PostgreSQL extractor)

August 31, 2019

Forewarning - loading data directly from the repository is not recommended. Most requirements can be met through the QRS APIs.

Repository Explorer 1024x450

There's a lot of tables, all qualified - plus an index table. Smart search and insights are recommended!

This script loads all QRS data from the repository into an app. The code is below, or the latest is available on GitHub.

// Qlik SenseRepository Explorer Script
// Version date: 27/06/2019
// This script loads each table from the repository to give you the ability to find field values and identify issues without downloading PgAdmin
// Even in big deployments this app shouldn't be too large, as we're avoiding the logs database

// Config steps:
// 1) Create a data connection to the repository and note the name. For a simple single node install:
//	Connection Type: PostgreSQL
//	Host name: localhost
//	Port: 4432
//	Database: QSR
//	User name: postgres
//	Password: defined during Qlik Sense install (superuser password)
// 2) Update the connection name in the script below, then paste it into an app and reload

// -- Script start
// Create a connection to localhost:4432 with postgres user and the superuser password

// Load the table listing from the default Postgres directory
	RowNo()			AS %KeyTableList,
    	table_catalog		AS [Table Catalog], 
	table_schema		AS [Table Schema], 
	table_name		AS [Table Name], 
	table_type		AS [Table Type];
FROM "information_schema"."tables"
WHERE "table_catalog" = 'QSR'		// Only load from repository tables
   	AND "table_schema" = 'public'	// Only load public tables

// Set a variable with the table count and print this to the console
LET vCount_Tables = NoOfRows('TableList');
TRACE >> Found $(vCount_Tables) tables in QSR.Public.;

// Create an empty table to concatenate table rows to
	null()		AS %KeyTableList
AutoGenerate 0;

// Now loop over these tables and load their contents!
FOR i=0 to vCount_Tables - 1

	LET vLoop_TableKey 	= Peek('%KeyTableList',i,'TableList');
	LET vLoop_TableSchema 	= Peek('Table Schema',i,'TableList');
	LET vLoop_TableName 	= Peek('Table Name',i,'TableList');
	TRACE >>> Loading from $(vLoop_TableSchema).$(vLoop_TableName).;
	// Set qualify statement for all Qlik data tables

	// Get the data from the table
	FROM "$(vLoop_TableSchema)"."$(vLoop_TableName)";
	// Set unqualify statement now that we've done the data load
	// Get a row count from the table and join back to the table listing
		'$(vLoop_TableKey)'		AS %KeyTableList,
		num(tablerows,'#,##0')		AS [Table Row Count];
		COUNT(*) as tablerows
	FROM "$(vLoop_TableSchema)"."$(vLoop_TableName)";
Next i;

// -- Script end

Profile picture

From Dave, who writes to learn things. Thoughts and views are his own.

© 2024, withdave.