topic Re: Problem with filter in QlikView

Problem with filter

Sat, 28 Jun 2014 15:16:01 GMT

I need to do somefing like that :

if (count(Contract id =1,'1',

if (count(Contract id =2,'2',

if (count(Contract id =3,'3',

if (count(Contract id =4,'4',

if (count(Contract id =5,'5','many'))))) as Number of contracts

So i need to be able to look how many contracts client have. My way does not work. Can you help me please?

swuehl — Sat, 28 Jun 2014 15:28:31 GMT

Try

LOAD KeyField,

if( count([Contract id]) > 5, 'many', count([Countract id]) ) as [Number of contracts]

RESIDENT YourTable GROUP BY KeyField;

You probably need a group by clause in your load statement when aggregating.

Also take care of closing functions like count() with a closing bracket, and use correct quoting of your field names when they contain e.g. spaces.

hic — Sat, 28 Jun 2014 15:28:41 GMT

You should make a chart (pivot table, bar chart, etc.) with Client as dimension and

If(Count(distinct [Contract id])>5, 'Many', Count(distinct [Contract id]))

as expression.

HIC

Sat, 28 Jun 2014 16:09:03 GMT

I need filter, so i can select only clients that buy 1 contract or 2 ans so on.

Sat, 28 Jun 2014 16:09:08 GMT

I need filter, so i can select only clients that buy 1 contract or 2 ans so on.

Sat, 28 Jun 2014 16:10:41 GMT

I need to select values, so i can see clients that buy only one contract and the other time i whant to see those who buy 3 contracts

hic — Sat, 28 Jun 2014 16:13:01 GMT

Then you should create a calculated dimension (expression in a List Box) according to

Aggr(If(Count(distinct [Contract id])>5, 'Many', Count(distinct [Contract id])),Client)

HIC

Sat, 28 Jun 2014 16:30:32 GMT

for some reason the system tell me that it is invalin expression