Search code examples
mysqlqlikview

QlikView Direct Discovery

hopefully someone can help me with QlikView direct discovery.

Problem: I've a database with two different tables with the same structure and indexes. Within the tbl_kri (kri stands for key risk indicators) are non-confirmed data, within tbl_kri_archive are confirmed data.

Table extract:
customer_id, kri1, kri2, kri3, run_id

run_id is related to a date in another table.

From a Qklikview point of view I would like to ask for all kri detail information for a selected customer and run_id, which is selectable in Qlikview.

In the MySQL database I can create a view like

SELECT * FROM tbl_kri
UNION ALL
SELECT * FROM tbl_kri_archive.

However, the union all will not take indexes into account and the run time is very bad. Several millions of records will be searched without using an index.

The integration in Qlikview is like DIRECT QUERY 

DIMENSION customer_id
 MEASURE kri1, kri2, kri3, run_id
 FROM tbl_kri;

But how can I implement the union to the tbl_kri_archive? Any idea? Thank you very much in advance Best regards Andreas

Solution

  • Fixed it!

    Problem was an underlaying MySQL / MariaDB Version 10.1. I've updated it to Version 10.2 and in this version the UNION will take also indexes into account.

    Best regards Andreas