topic Re: HOW FILTER A WORD in QlikView

HOW FILTER A WORD

Anonymous — Fri, 01 Aug 2014 14:37:08 GMT

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

Re: HOW FILTER A WORD

its_anandrjs — Fri, 01 Aug 2014 14:41:14 GMT

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

Re: HOW FILTER A WORD

Anonymous — Fri, 01 Aug 2014 14:43:01 GMT

Done !

Re: HOW FILTER A WORD

giakoum — Fri, 01 Aug 2014 14:45:11 GMT

load the file into QlikView and then use wildmatch function

Re: HOW FILTER A WORD

its_anandrjs — Fri, 01 Aug 2014 14:54:57 GMT

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

Re: HOW FILTER A WORD

rustyfishbones — Fri, 01 Aug 2014 15:24:25 GMT

Hi Matheus,

Try using Wilmatch

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

see the attached file based on your txt file

Regards

Alan

Re: HOW FILTER A WORD

its_anandrjs — Fri, 01 Aug 2014 15:49:49 GMT

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

Re: HOW FILTER A WORD

Anonymous — Fri, 01 Aug 2014 16:41:52 GMT

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

Re: HOW FILTER A WORD

Anonymous — Fri, 01 Aug 2014 16:42:39 GMT

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

Re: HOW FILTER A WORD

Anonymous — Fri, 01 Aug 2014 19:07:38 GMT

up!

Re: HOW FILTER A WORD

MarcoWedel — Fri, 01 Aug 2014 21:44:22 GMT

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

Re: HOW FILTER A WORD

Anonymous — Mon, 04 Aug 2014 13:52:19 GMT

Thanks Marco !

This is a solution for my problem.