Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Not applicable

Advanced Search

Hi guys

If I have a list box with SSN - xxx Xx xxxx and I want to look at (select) the cases where the 4th number is higher then 2.

How can I do this when opening the advanced search by CTRL+SHIFT+F?

Br
Ince

1 Solution

Accepted Solutions
MVP
MVP

Re: Advanced Search

If your value is formatted like shown above, you need to also count the spaces, then this should filter your data correctly, I think:

=Mid(SSN,12,1)>2 and len(Mid(SSN,5))=11

You can also create a flag in the script using this logic, then just filter your data in the frontend using that flag.

4 Replies
MVP
MVP

Re: Advanced Search

Maybe something like

=Mid(SSN, 5,1) >2

(assuming the 4th number is 5th character in your textual representation).

Not applicable

Re: Advanced Search

Hmm ..

So..

Let say I have a list box/field called: SSN

I have 3 rows in that field:

A - 111 22 2222

B - 111 22 3333

C - 111 22 4444

I want to select only B and C, because the  6th number/8th character is higher then 2, by usind advanced search: =mid(SSN, 8, 1) >2 - this is correct, right?

One more thing how can I make sure that it only look/select the numbers with 9 digits? I have also client id which is based on 6 numbers instead of 9 numbers as SSN?

MVP
MVP

Re: Advanced Search

If your value is formatted like shown above, you need to also count the spaces, then this should filter your data correctly, I think:

=Mid(SSN,12,1)>2 and len(Mid(SSN,5))=11

You can also create a flag in the script using this logic, then just filter your data in the frontend using that flag.

Not applicable

Re: Advanced Search

Great! It works! Thank you