Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Counting a filed that starts with a certin letter

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Counting a filed that starts with a certin letter

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

7 Replies
MVP
MVP

Re: Counting a filed that starts with a certin letter

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

Not applicable

Re: Counting a filed that starts with a certin letter

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

MVP
MVP

Re: Counting a filed that starts with a certin letter

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

Not applicable

Re: Counting a filed that starts with a certin letter

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

MVP
MVP

Re: Counting a filed that starts with a certin letter

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

aadilmadarveet
Valued Contributor

Re: Counting a filed that starts with a certin letter

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

Not applicable

Re: Counting a filed that starts with a certin letter

it worked !
thx man

Community Browser