Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vyacheslavg
Contributor III
Contributor III

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

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.

13 Replies
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
Contributor III
Contributor III
Author

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
Master
Master

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.
vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
Anonymous
Not applicable

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
Contributor III
Contributor III
Author

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
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
vyacheslavg
Contributor III
Contributor III
Author

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?