topic Re: Search like expression in QlikView

Search like expression

Wed, 09 Jul 2014 18:00:16 GMT

Hello Experts,

It seems an easy one, but still having hard time to find a solution for it. I have a field say:

Name

Redmond Lee

Pete Lukas

Raman Pit

Redmond Aurther

....

Now in the front -end itself I need a list box using expression that would give me names - being first name same. So in this case my output listbox would contain -

Redmond Lee

Redmond Aurther

I tried with couple of aggr() combinations but with no luck. Any help would be much appreciated.

Note: I would like to avoid valuelist()/valueloop().

Thanks, Amit

Re: Search like expression

whiteline — Wed, 09 Jul 2014 18:39:24 GMT

Hi.

You can add a separate field for the first name. Then you can easily count the references and keep the names that have more than one reference.

=aggr(Only({1<[First Name]={'=Count({1} Name)>1'}>} [First Name]), [First Name])

Re: Search like expression

Wed, 09 Jul 2014 18:47:04 GMT

Thanks for the reply. Yes that's an option - but I have to go back to the script for that. Could you think of anything else -absolutely in the front-end?

Re: Search like expression

jerem1234 — Wed, 09 Jul 2014 19:43:56 GMT

If you are looking for just front end, you may try something like:

=if(substringcount(concat({1}total distinct Name, ','), subfield(Name, ' ', 1))>1, Name)

in your list box expression.

This concatenates all names together, then if the first name appears more than once in this list, it will display the Name.

Hope this helps!