topic Re: Filtering Data within a List Box in QlikView

Filtering Data within a List Box

Mon, 23 Mar 2015 22:18:27 GMT

Hi all,

Greetings!! I am pretty new to QlikView. Please do help me out in this issue. I have this list box with over a thousand rows. Almost each row has a number like '22-3-01' as a part of the sentence like Xxxxxxxx 22-4-02 xxxxxx. I want to see the all the records which match this number format. I find it very cumbersome to filter the records by entering the above number one by one.I want to see all of them together. I tried using the function

=if(match(Column/Field Name, '22-03-01', 22-05-08',etc (15 more values)), Column/Field Name)

But it's not working.

Seeking your advice on this

Re: Filtering Data within a List Box

robert_mika — Tue, 24 Mar 2015 00:46:56 GMT

Is the any pattern within your sentences so the numbers are in the same place or after specific character (space like in example above).

Then you can use any of Text function to extract your data.

post more examples to see if that will work

Re: Filtering Data within a List Box

oknotsen — Tue, 24 Mar 2015 10:06:19 GMT

Are they always after a set position in the string?

If so, you could use a "mid" function. Example: mid('abcdef',3, 2 ) returns 'cd'.

Or are they always after a set character?

That add an "index" function at the spot of the "3" in the previous example. Example of index: index( 'abcdabcd', 'b', 2 ) returns 6.

Hope that helps.

Re: Filtering Data within a List Box

Tue, 24 Mar 2015 12:07:50 GMT

Hi Robert and Onno,

Greetings!! Thanks a lot for your replies. The problem is that there is no specific pattern to the text in each record. The position of the number changes every time. For ex it maybe "xxxxxx xxxxxxxx 22-3-02 xxx xx xxx" in one case and be like "xxx xxxx xx x 22-3-02 xxx xxx" in another case and so on. There is no set pattern or order unfortunately

Re: Filtering Data within a List Box

oknotsen — Tue, 24 Mar 2015 12:24:37 GMT

Are the dashes between the numbers (the -'s) only at that spot in the string or are they used in the rest of the text?

If they are unique in the string, you can do the math from there (using that index function from my previous reply -2).

Re: Filtering Data within a List Box

robert_mika — Tue, 24 Mar 2015 12:34:06 GMT

Try:

=textbetween(mid('xxxxxx xxxxxxxx 22-3-02 xxx xx xxx',index('xxxxxx xxxxxxxx 22-3-02 xxx xx xxx','-',1)-2),'',' ')

t1:

load * Inline

[pattern

"xxxxxx xxxxxxxx 22-3-02 xxx xx xxx"

"xxx xxxx xx x 22-8-02 xxx xxx"]

;

t2:

load

textbetween(mid(pattern,index(pattern,'-',1)-2),'',' ')as Pattern

Resident t1;

drop table t1

Re: Filtering Data within a List Box

Tue, 24 Mar 2015 12:37:06 GMT

Hi Onno,

The dashes are only present when there is a number

Re: Filtering Data within a List Box

Tue, 24 Mar 2015 12:55:06 GMT

Hi Robert,

Greetings!! I have 15 more different numbers like 22-3-02. Some of them are 22-17-02, 32-9-02 and so on. Each one is different. Is there a script for it to filter the records which contains the numbers along with the text? That might be pretty useful

Re: Filtering Data within a List Box

Tue, 24 Mar 2015 13:09:20 GMT

Hi Onno,

Re: Filtering Data within a List Box

robert_mika — Tue, 24 Mar 2015 14:01:47 GMT

Did you try to solution I have posted?

Does it work?

If not could you post more of your samples?