Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
so those set analysis do seem to deliver the data in question.
heres another fundamental and my 'will this work' attempt at an answer
i want to show not a sum but each individual data point.
do i have to put something in the dimension tab to handle this and use avg in the expression?
to make each point unique and not aggregate i need qlik to consider multiple columns.
in my real data table i have date, parameter, customer, paddock
i want one data point for a each unique value of (date-customer-paddock)
im guessing i need to either
combine these in 'calculated dimension' - but im not sure how to combine them
or
have 3 different dimensions - but this doesnt seem to work
i think im nearly there as 'Sum({<parameter={'A'}>} value)' in X and Sum({<parameter={'B'}>} values) creates the axes i want
your time is much appreciated!
The data you posted indicates that each record is identified by a unique ID value. If you use the field ID as dimension then sum(Value) will return the sum of Value per ID, which is the same as the value of Value for that ID. If you don't have a unique ID for each record then you can generate such a field when you load the data with a preceding load:
CONNECT ...your data source here ... ;
MyQVTable:
LOAD *, RecNo() as UniqueRecordIdentifier;
SQL SELECT ID, Date, Parameter,Value FROM mytable;
OK Alex, my QVW consisted of the following script:
DataTable:
LOAD * INLINE [
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
];
AData:
LOAD
Date,
Value as AValue
Resident DataTable
Where Parameter = 'A';
CData:
LOAD
Date,
Value as CValue
Resident DataTable
Where Parameter = 'C';
Drop Table DataTable;
Then use a scatter chart as follows:
that seems to have worked.
but for some reason the scatter chart is making the new expressions appear as lines. this results in a blank graph