How to run custom sql query in qlik sense?

[alex_202]

I am creating a dashboard on qlik sense, and my dashboard utilizes multiple tables and I have customize sql queries (with multiple subqueries) which returns final filtered data tables for my visualization. I aware that qlik sense not support entirely sql convention and I am not sure qlik sense allow me to run custom sql queries once I loaded tables that needed for my dashboard.

in my current attempt, I loaded all different tables into qlik sense and flatten them out into one data table using qlik sense association properties which is recommended by qlik sense, but resulted dashboard is not insightful because I compared it with benchmark dashboard. 

I am just curious, once I loaded all different tables into qlik sense and flatten them out into one, does qlik sense allow me to run custom sql queries on top of it so I can get right data for my dashboard? Can I encapsulate custom sql query as qlik sense convention and run it and get the data for my dashboard? Is that possible? Does qlik sense support this demand?

[marcus_sommer]

Qlik doesn't run the sql else it transferred the query-string per your applied driver to the database and received on this way back the query-results. Any limitations in regard to your sql will be caused from the capabilities and the configurations from the used driver + database.

As far as you could store certain values within variables you could integrate them within the query-string. Such things may be useful for a few values like the max. ID or some period-values within where-clauses. But you could not join or filter the sql within the queries with Qlik tables/fields. After the data are pulled into Qlik you could do that but not within the query.

I hope it clarified the matter a bit.

- Marcus

[alex_202]

Hi @marcus_sommer :

Thanks for answering my question. If we flatten out multiple tables into one, is there any workaround to subset data out of it? What approach should I pursue? Does QlikSense provide any subsetting functionality for flattened data model? Do you suggest any ideas on those? Thank you

[marcus_sommer · Accepted solution]

If data are concatenated (union in sql) it's quite practically to add a source-information to each part, like: 'fact x' as Source which could be later used to select the wanted subset and/or to use it as set analysis condition within the expressions. If data are joined or mapped you may add the source-information with a prefix/suffix to the field-names but usually this is only by a few fields sensible like multiple prices or dates and even there it might be better to name them user-friendly instead specifying from which source-table they are coming.

Beside this you may also consider to tag and/or to comment your tables and fields with such information and/or loading them as a real meta-data information table into the datamodel.

- Marcus