Counting a filed that starts with a certin letter

[#1]

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

[swuehl]

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

[#3]

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

[swuehl]

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:

http://www.qlik.com/us/explore/resources/how-to-videos?language=english&page=5#%2Fus%2Fvideos%2Fhow-...

[#5]

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

[swuehl · Accepted solution]

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 ...;

[Anonymous]

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];

[#8]

it worked !
thx man