SQL in visualization (for example, instead of set analysis)

[vyacheslavg]

Hello everyone,

I know that SQL can be used in data load scripts in QS (there several threads on this topic here)

What about expressions, for example instead of set analysis?

Example

In pseudo language:

Provide SUM of Sales where Volume is equal or more than 200.

in QS Set analysis script this is:

Sum({$<Volume ={">=200"}>} Sales)

Is it possible to write the equivalent SQL statement in expression?

The reason is that sometimes I am completely lost with set analysis grammar.

I review these 200-300 + character formulas with set modifiers, set operators and set identifiers and afraid to unwillingly summon some set analysis demon from parallel dimension executing this script.

[vinieme12]

Because it would be a waste of time writing nested sql statements

Not all expressions will be as straightforward as a simple sum (sales)

Try imagining to recreate the functionality of firstsortedvalue () using SQL!

Even microsoft has MDX and DAX,similarly Set Analysis simplifies things

You feel sql is easy because you know it , once you learn something everything becomes easy.

[vyacheslavg]

Sure, thanks. Every software has their pros and cons, and their unique approaches.

Anyway, still a lot can be done by SQL in the right place (creating views for consumption by QS or in the load scripts of QS itself)

[Lech_Miszkiewicz]

Why not? Because in my opinion it would be a step back for Qlik.

If other tools with SQL based visualisations are efficient, why dont we hear about them among market leaders? I guess it is because such a solution is not flexible. I agree that it is possible though!

Now to more important thing. Implementing SQL script in QS expressions is impossible due to how associative Qlik engine works. If you would have done SQL on UI you would loose all associative benefit from Qlik product which is its very stron part.

You also very clearly stated that you think of SQL as of querry language and I agree with you. Qlik Sense front end is not querry. It is whole, integrated datamodel. To make it working consisten you need to either apply SQL querry on all objects - which ultimetly means querry on whole datamodel which then can also be done on back-end.

I would give it another think-go.

On the other hand on script level i prefer to use SQL and i do it, mainly because it deals a lot faster with statements like "Where "and especialy "Group by". Still - end result needs to be full data model.

regards

Lech

[ahaahaaha]

Hi Vyacheslav,

Several considerations.

1. I have never seen in the plans of developers use in the visualizations of the language SQL. Now this is not and it is unlikely to be in future. Discussion of this topic does not make sense - it is unlikely that the developer will change the concept of the system for the sake of convenience of several users.

2. Practice has shown that the possibility of using the language SQL directly in scripts very much depends on the developers of drivers SQL (different drivers support a different set of supported commands and functions). In my opinion, that the data must be directly collected in Qlik (well, it can be imposed restrictions on the amount of data, if necessary). Further all processing should be carried out by means of Qlik. This is just my personal opinion, no more.

Regards,

Andrey