topic Re: Implementing MySQL (cross-schema) query into Qlik Sense in Connectivity & Data Prep

Implementing MySQL (cross-schema) query into Qlik Sense

GoosJ — Mon, 20 Feb 2023 08:25:48 GMT

Hi All,

Recently, I got a connection to our ERP (MySQL database) from Qlik Sense. The database has a different schema for each of our customers. When I work on the database directly (MySQL Workbench, I have a working query that basically runs cross-schema. So I have one statement that is being executed for all customers without rewriting the same query over and over. See attached file, "Cross Schema Query".

I know that Qlik Sense can also execute SQL queries, but when I try to run this query (Qlik Sense Cross Schema) I run into multiple issues:

1. The "SQL SET SESSION group_concat_max_len = 100000;" doesn't execute as I am only allowed to run SQL Select statements

2. When I try to run without changing the max_len, I only get 1 row as a result. When I check the result it looks like my query gets cut off at a certain point. So it seems that it is not executing the complete query, maybe because of not having the neccesary max_len?

At this point I am not exactly sure how to get this working. Our ERP doesn't want us to create views or stored procedures on the database, so I have to get it working within the Qlik environment.

Thanks in advance.

Re: Implementing MySQL (cross-schema) query into Qlik Sense

carlos_molino — Mon, 27 Feb 2023 21:45:07 GMT

I don't quite understand the problem.

If the connection is a MySQL database, you could directly perform a query of this type without problems:

[TABLE_A]:
LOAD
* ;

select
*
from `schema`.`table_a`;

and you would have in the Qlik TABLE_A all the data returned by the query.

Re: Implementing MySQL (cross-schema) query into Qlik Sense

williejacobs — Tue, 28 Feb 2023 06:41:23 GMT

Hi @GoosJ

Maybe start with a simple select as suggested by Carlos.
For the second part maybe create a list of the schemas you need to extract the data from.

Then try the following,

Let vRows = NoOfRows('SchemaList');
For i = 1 to vRows
Let vSchema = FieldValue('device_id',$(i));

You can then pass the schema into your request as a variable,
after your select / load add

Next i

This will loop trough your Schemas listed.

Hope this helps.