How to limit the number of rows displayed in my table

[#1]

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?

[#2]

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.

[#3]

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

[#4]

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

[#5 · Accepted solution]

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')

[#6]

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.

[#7]

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.