Struggling with basic concepts - creating graphs from data using SQL code

[#1]

Coming from a Non BI tool environment i am struggling with a lot of what i assume are basic concepts as i have been using my own dashboards in excel

Everything from dimensions vs expressions and where do i put code to achieve what i want.

main difficulty is, i know what i want to achieve, and how to do it.. but where do i put my code, where do i put my variable

for example today all i want to do is create a scatter graph using data from a SQL select statement.

im so lost in all of this i am at the point of using SQL to generate another table and just loading this table into qlikview

hopefully someone can help if i explain what i want to do..

i have a narrow-deep datatable

Date, Parameter, Value

using an sql statement i want to create two arrays ArrayA, ArrayB, from this data table containing numerical data from 'Value'.

from here i want to make a scatter graph where i would use arrayA data as X axis values and ArrayB data as Y axis

1. i dont know where to put the SQL statement - in the chart?, where in the chart?  in the code where the tables are called?

2. is this even the way you use qlikview or not

i could achieve the above in excel in minutes, but in qlikview, i havent a clue what goes where.

[Gysbert_Wassenaar · Accepted solution]

You put the sql statement in the script

CONNECT ...your data source here ... ;

MyQVTable:

SQL SELECT Date, Parameter,Value FROM mytable;

That should create a table named MyQVTable with the fields Date, Parameter and Value. You can use this table to create a scatter chart. For example with Date as dimension and two expressions like sum({<Parameter={'A'}>}Value) and sum({<Parameter={'B'}>}Value).

If you'd like more help then please post some example data. An excel file is fine, especially if it also contains the result your after.

[#3]

Thank you,

from your post alone i now know that to create a table you what syntax to use.

Excuse my ignorance, what is the purpose of the  {} and <> in that statement? i see them everywhere but never know exactly what they are for?

ill try to explain what i m trying to do hopefully without the need for excel file

ID, Date, Parameter, Value

1, 2016-03-01, A, 1

2, 2016-02-01, A, 2

3, 2016-01-01, A, 4

4, 2016-03-01, B, 10

5, 2016-02-01, B, 9

6, 2016-01-01, B, 15

7, 2016-03-01, C, 100

8, 2016-02-01, C, 102

9, 2016-01-01, C, 110

The above is my data table structure (in much simpler form).

i need to plot A against C where each pair of points have same date and parameter

the 3 points above would be

(1,100)

(2,102)

(4,110)

my idea was to create two queries, that read

Select value

from mytable

where parameter = 'A'

order by date;

Select value

from mytable

where parameter = 'B'

order by date;

where date would be the link between the join

but im not sure if i should achieve this by arrays, new table, etc etc

its the case where i know what i want but dont know how to do it

[r3iuk]

Does the attached example do what you're after?

[squeakie_pig]

Qlik has a lot of free resources that will help you understand the basics.

Try some of the links on this page: New to QlikView Videos

[Gysbert_Wassenaar]

Excuse my ignorance, what is the purpose of the  {} and <> in that statement? i see them everywhere but never know exactly what they are for?

The are part of the syntax for crafting set analysis expressions. See these blog posts:

• Why is it called Set Analysis?‌
• A Primer on Set Analysis

[#7]

sorry, i should have mentioned. i am using personal desktop  (as a trial for work) so i cannot access qvw files

[#8]

Ok, i had heard of set analysis, but didnt get what it was.

seems like that is what im after.

looks like i have more learning to do in general before i start actually being capable of being productive in this tool!

But i get the basics now of set analysis and what you now described in your first reply.

some of the more complex set analysis code still are a bit complex

[jaspal_icon]

Hello Alex,

{<>} This is the syntax for Set Analysis, power of QlikView dynamic feature to calculate, aggregate or filter the data with respect to QlikView Associative data model. As you looks like first time user, I will suggest to just press F1 in QlikView itself and search for Set Analysis and you will find the very good detail on that.

This set analysis if faster , so you can use two expression like this:

Sum( if(Country='Canada', Sales))

or

Sum({<Country={'Canada'}>} Sales)

Second Expression is faster then first.

I hope it will be helpful.

Thanks

[Gysbert_Wassenaar]

some of the more complex set analysis code still are a bit complex

Yeah, don't let that scare you though. Just take it a step at a time. I took me a good half year to get a good understanding of set analysis too.