topic How to limit the number of rows displayed in my table in QlikView

How to limit the number of rows displayed in my table

Wed, 30 Nov 2011 10:20:36 GMT

Hello,

I'm new to Qlikview so already my appologies if I'm asking noob questions.

I have a database which contains development requests. In the request the builder is entered by the user. This field can contain multiple values (free text).

Now I want to create a table with only the values for a certain builder.

Example:

builder	request number
Tom	1
tom, peter	2
Tom Lismont, Peter, John	3
John, tom	4

In my table only the rows for Peter should be displayed. Anyone can help?

How to limit the number of rows displayed in my table

Wed, 30 Nov 2011 10:46:15 GMT

One method would be to create a straight table and use a calculated dimension with the following expression:

=If(index(lower(builder), 'peter')> 0, builder)

This searches for any occurrence of Peter (case insensitive).

Of course the drawback is that your are restricted to searching for 'Peter' unless you change the code.

How to limit the number of rows displayed in my table

Wed, 30 Nov 2011 10:52:55 GMT

Ok and now for the super noob question: how to create a straight table? In my table I tried creating adding a calculated dimension with your code but the result is that I still get all the records. Where Peter is the Builder, the name is showed and in all the other cases there is a '-'.

Re: How to limit the number of rows displayed in my table

Wed, 30 Nov 2011 11:01:40 GMT

I uploaded the qvw so you can have a look what I'm talking about.

Re: How to limit the number of rows displayed in my table

Wed, 30 Nov 2011 11:14:10 GMT

You already have a straight table. To hide all the non Peter lines, select your calculated dimension on the Dimension tab and halfway down the right side select 'Suppress When Value Is Null'. This will filter out the non Peter records.

If you only want to see Peter (rather than for instance Ed van Daal; Peter Pesch) change your calculated dimension to read:

=if(index(lower(Builder),'Peter')>0,'Peter')

Re: How to limit the number of rows displayed in my table

Wed, 30 Nov 2011 11:17:47 GMT

Also once you have created your calculated dimension and filtered by it, you can choose to hide it by going to the Presentation tab, selecting your column and changing the status from Show Column to Hide Column.

How to limit the number of rows displayed in my table

Wed, 30 Nov 2011 17:39:04 GMT

Can I mention that it's poor database design the way the origional table is setup and you will run into more and more problems down the road. Someone can enter Pete, Peter, Petey all meaning the same thing, it gets worse if they start entering (butchering) last names. I am speaking from experience free form text fields are evil.

If you have the ability to do something about the source try to.