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.

[OmarBenSalem]

It would be sthing like this in the script

load YourDimension,

sum(Sales) as SumSales

from YourTable where Volume>=200 group by YourDimension;

[vyacheslavg]

I was looking at Periscopedata and Chartio - it looks like they support SQL for visualization. Looked pretty impressive, at least on demo level, especially Periscope stuff.

As I understand, the approach of "traditional" BI tools is to focus on UI (drag-and-drop) or internal scripting (Qlik, DAX, etc.), but it would be cool to see SQL support in visualizations in Qliksense...

[beck_bakytbek]

Hallo Vyacheslav,

normaly you can use all SQL statements within Script-Area, and they do normaly work,

to your issue:"The reason is that sometimes I am completely lost with set analysis grammar.";look at this, i am sure that helps you: (uveren pomoshet tebe)

Вебинар "QlikView: работа с Set Analysis и альтернативными состояниями" - YouTube

i hope that helps

beck

[Lech_Miszkiewicz]

Hi

As with any language you need to learn syntax and it will not be a problem in the future to understand what you are writing. I know Set Analysis can be scry sometimes.

Writing SQL on UI is not possible in Qlik Sense as it does not support this language in expressions.

You can load data with this query and then in QlikSense use sum(Sales) expression. Obviously then you loose bunch of information - sales with Volume < 200.

As an alternative you can concatenate this querry to original dataset.

regards

Lech

[vinieme12]

No you can't  you will need to learn set analysis just as you learnt sql

Once you learn something it becomes easier to work with

There are lots of content around set analysis on the community

And this wizard if it helps you

Set Analysis Wizard for QlikView | qlikblog.at

[Anonymous]

Hi,

It'll be good if you learn set expressions. You can do all your limitations on your load script but if you want to go back in your data in the future it'll be impossible to do it. So give some effort and learn set expression.

Best regards,

Ogün ADSAY.

[vyacheslavg]

Gents,

Thanks for your responses.

I was not asking for resources to learn the set analysis or advice "that you need to learn something".

I'm quite comfortable with set analysis, but I'm not a full-time QS developer to invest my time into all possible details  and depths on syntax/grammar of it.

I was just asking for possibility to use SQL in visualization _together_ or _instead of_ set analysis syntax - that's it.

For example, Omar's response is a good hit on my answer.

[vinieme12]

Omar response was in terms of the load script in the data load editor? Not expression building

[vyacheslavg]

Yes, it was.

And it was the most helpful response so far, even it describes the limits of SQL usage in QS.

My line of thinking is that SQL is de-facto query language, it is everywhere and it is humanly readable, even years after you've developed something. There are several systems on the market which use sql to create visualization, in a very efficient manner.

Why not enable to do this in Sense?