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.

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
LIB CONNECT TO 'PostgreSQL_QSR';

// Load the table listing from the default Postgres directory
TableList:
LOAD 
	RowNo()			AS %KeyTableList,
    	table_catalog		AS [Table Catalog], 
	table_schema		AS [Table Schema], 
	table_name		AS [Table Name], 
	table_type		AS [Table Type];
SELECT 
	"table_catalog", 
	"table_schema", 
	"table_name", 
	"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
TableRows:
LOAD
	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
	QUALIFY *;

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

// -- Script end