Direct Query

[S_N_Banerjee]

Hello,

I am working in a project where customer is interested in DIRECT QUERY in Qlik Sense.

I faced with some issues. There is one table (1.3 million plus rows) in customer's data base which should be loaded through DIrect Query in ONE App of Qlik Sense.

The time of respond is huge and sometimes my app crashed, nothing was working. Getting error that there are too many rows to display.

Kindly Help with proper syntax and Direct Query guidance for qlik sense. Also let me know if there should be any changes from admin side also to execute such queries.

[pedrobergo]

Hi @S_N_Banerjee 

Direct Query is not a good solution for Qlik Sense. I recommend you to use ODAG/Dynamic View or Document Chainning to fit your needs. ODAG/Dynamic View will be make SQL instruction such as Direct Query.  See this video with this feature https://www.youtube.com/watch?v=Ft3I00-g4H4 

I'm telling you it because Qlik will doesn't make any investment to solve DQ limitations (it has many), you can check it at https://help.qlik.com/en-US/sense/May2021/Subsystems/Hub/Content/Sense_Hub/DirectDiscovery/access-la... 

[],

Pedro

[S_N_Banerjee]

Thanks @PED 

Kindly let me know how to add more than on tables in Direct Query

[pedrobergo · Accepted solution]

Hi @S_N_Banerjee 

Yes, advising you that crashed is often... You just convert the SQL Statemant to this structure:

DIRECT QUERY
DIMENSION field1, field2, field3 <- Insert the fields that you wanna use in charts
MEASURE measure1, measure2 <- Insert the fields that you wann use in charts
DETAIL field4, field5 <- Insert the fields that you wanna use in table details, you cannot use them within charts
FROM table1
JOIN table2 ON table1.key = table2.key <- You can use LEFT, RIGHT, FULL join as you need
WHERE fieldx LIKE 'A%'

Remember that you cannot use aggregated functions at MEASURE, like SUM, AVG such as GROUP BY within FROM clause, so maybe you should to create a VIEW at Database and just point to it.

Another point is that you can't use alias for tables, and can't use subqueries.

You can combine DQ with in-memory tables, used by LOAD statement. So, the dimensions tables must be loaded to Qlik Memory, and fact tables will use DQ. The dimensions will not overhead the database and avoid limitations of DQ. Think that each object will query the database, so if you have 3 filters and 1 chart, it will be 4 queries at each click.

More details about MultiTables at DQ at https://help.qlik.com/en-US/sense/May2021/Subsystems/Hub/Content/Sense_Hub/DirectDiscovery/multi-tab...

[],

Pedro

[ca386807]

Any idea why I'm getting a 'Could not parse SELECT statement.' error when using the DIRECT QUERY syntax formatting that you showcased above?

I have:

DIRECT QUERY DIMENSION

Owner,
Description,
Is_Private,
Created,
Updated

FROM UserRepositories;

See screenshot below

Thanks for your time!