Solved: Re: HOW FILTER A WORD - Qlik Community

Anonymous · ‎2014-08-01

Hello guys!

See if you can help me ...

-I have a field called Text.

-I need to filter all rows in that field, through a keyword 'CNPJ' and 20 more characters in front of the word.

-After filtering need to put them in a field called 'CNPJ-Filtered'

How to do this in QlikView?

Follows the type of file that receives the field called Text.

Many thx...

MarcoWedel · ‎2014-08-01

Hi Matheus,

one solution could be:

tabInput:
LOAD Text,
    If(WildMatch(Text, '*CNPJ*') and WildMatch(TextNumPattern, '*00.000.000/0000-00*'),Text) as [CNPJ-Filtered];   
LOAD @1 as Text,
    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(@1,
      '1', '0'),
      '2', '0'),
      '3', '0'),
      '4', '0'),
      '5', '0'),
      '6', '0'),
      '7', '0'),
      '8', '0'),
      '9', '0') as TextNumPattern
FROM [http://community.qlik.com/servlet/JiveServlet/download/580793-118485/DO8687_02_06_2014.txt]
(txt, codepage is 1252, no labels, delimiter is ';', msq);

hope this helps

regards

Marco

View solution in original post

its_anandrjs · ‎2014-08-01

If you share sample data will be more easy to help then please provide in any inline table.

Anonymous · ‎2014-08-01

Done !

giakoum · ‎2014-08-01

load the file into QlikView and then use wildmatch function

its_anandrjs · ‎2014-08-01

Try with Index function and load your table as two times on without CNPJ and in second with CNPJ and put into a field

[CNPJ-Filtered].

Otherwise you can create o filter for this also for filter the CNPJ

FirstTab:

LOAD

@1 as [Without-CNPJ]

FROM

DO8687_02_06_2014.txt

(txt, codepage is 1252, no labels, delimiter is ';', msq) Where Not Index(@1,'CNPJ');

SecondTab:

LOAD

@1 as [CNPJ-Filtered]

FROM

DO8687_02_06_2014.txt

(txt, codepage is 1252, no labels, delimiter is ';', msq) Where Index(@1,'CNPJ');

Let me know by this if works

rustyfishbones · ‎2014-08-01

Hi Matheus,

Try using Wilmatch

IF(WILDMATCH(@1, '*CNPJ*'),@1) AS [CNPJ:Filtered]

see the attached file based on your txt file

Regards

Alan

its_anandrjs · ‎2014-08-01

Another way also if you need the filtered and not filtered field in single table then load table as mentioned below with your given data.

LOAD

@1,

@1 as [With-CPNJ],

if( Index(@1,'CNPJ') >= 1 or Index(@1,'cnpj') >= 1 , @1 ) as [CNPJ-Filtered]

FROM

DO8687_02_06_2014.txt

(txt, codepage is 1252, no labels, delimiter is ';', msq);

Anonymous · ‎2014-08-01

CNPJ unable to filter the solutions presented, but I need to filter not only the word 'CNPJ' but also requesting that you immediately forward this pattern of numbers:

xx.xxx.xxx / xxxx-xx

Trying to demonstrate better ....

These lines were not to appear in the filter:

'' (National Corporations - CNPJ tenderer in case of corporate law)''

'' (notary and National Register of Legal Entities -. CNPJ)''

'' (e) Copy of registration card in the National Register of Legal Entities (CNPJ);)''

Only those who have this pattern of numbers:

'' CNPJ No.. 15.579.196/0001-98, with approval of the FUND INVESTMENT''

'' CNPJ: 33.120.031/0001-35''

'' SOUTH CNPJ / MF under No. 26.857.516/0001-40 - MANAGER OF''

I hope I have explained better now.

Thanks to all

Anonymous · ‎2014-08-01

CNPJ unable to filter the solutions presented, but I need to filter not only the word 'CNPJ' but also requesting that you immediately forward this pattern of numbers:

xx.xxx.xxx / xxxx-xx

Trying to demonstrate better ....

These lines were not to appear in the filter:

'' (National Corporations - CNPJ tenderer in case of corporate law)''

'' (notary and National Register of Legal Entities -. CNPJ)''

'' (e) Copy of registration card in the National Register of Legal Entities (CNPJ);)''

Only those who have this pattern of numbers:

'' CNPJ No.. 15.579.196/0001-98, with approval of the FUND INVESTMENT''

'' CNPJ: 33.120.031/0001-35''

'' SOUTH CNPJ / MF under No. 26.857.516/0001-40 - MANAGER OF''

I hope I have explained better now.

Thanks to all

Anonymous · ‎2014-08-01

up!