Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys!
i'm new to QlikView and couldn't find anything in the manual,
trying to make a pie chart for a certian field, the field is divided to three groups one starts with 'GW' and 'PP' the other with 'A' and the last with a number ranging from 1-9.
i need to count each record of thess groups apart and disply in a single pie chart
any ideas ?
thanks alot
If you have a working SQL SELECT statement, then you can just use a preceding load:
LOAD *,
if([Billing ID] like 'GW*' or [Billing ID] like 'PP*', 'Online'
, if([Billing ID] like 'A*', 'Gift','Credit')) as GROUP;
SQL SELECT ... FROM ...;
You need to assign common group values to your different field values.
You can do this in a calculated dimension, but it's better to do this already in th script when you load your data into QV.
For example, you can do it like
LOAD *,
if(FIELD like 'GW*' or FIELD like 'PP*', 'Group1'
, if(FIELD like 'A*', 'Group2','Group3')) as GROUP
INLINE [
FIELD
GWA
GWB
PPC
ABC
ACB
1B2
2CC
];
Then you only need to create a pie chart object with dimension GROUP and expression
=count(FIELD)
The inline table is just to load some sample data, you should replace the part starting from INLINE with
FROM YourTable;
And replace FIELD with your field name and 'Group1' etc with group names that are more meaningful.
Hope this helps,
Stefan
thx Stefan!
But i'm not sure i understand how to write the syntax, I wrote it in the edit script option and this is what i got :
Cannot open file 'C:\Users\orhas\Documents\Billing Options' The system cannot find the file specified.
LOAD *,
if([Billing ID] like 'GW*' or [Billing ID] like 'PP*', 'Online'
, if(FIELD like 'A*', 'Gift','Credit')) as GROUP
From [Billing Options];
it seems like it's trying to open a new document but all my info is on a sql server.
any ideas whats wrong??
To connect to an sql server, you need a ODBC or OLEDB connection i.e. a ODBC / OLEDB CONNECT statement with an appropriate connect-string.
If you have created a data source using the Windows administration tool, it's quite easy to connect using the QlikView Wizards on data tab of the script editor.
Maybe this is somewhat helpful:
I'm allready connected to an OLEDB and loaded my data with an SQL select statement
perhaps i should define the groups in a calculated dimension as you suggested before ?
sorry this is all very new to me....
If you have a working SQL SELECT statement, then you can just use a preceding load:
LOAD *,
if([Billing ID] like 'GW*' or [Billing ID] like 'PP*', 'Online'
, if([Billing ID] like 'A*', 'Gift','Credit')) as GROUP;
SQL SELECT ... FROM ...;
i think.. the below shoul dbe a resident if you already loaded a table..
LOAD *,
if([Billing ID] like 'GW*' or [Billing ID] like 'PP*', 'Online'
, if(FIELD like 'A*', 'Gift','Credit')) as GROUP
Resident [Billing Options];
it worked !
thx man