topic Filter in QlikView

Filter

Anonymous — Wed, 06 Aug 2014 14:03:09 GMT

Hello guys

I'm having difficulty in solving this problem, see if you can help me ...

I have attached the file I'm reading, based on it need to filter two numerical patterns.

"xxx.xxx.xxx-xx"

"xx.xxx.xxx/xxxx-xx"

example:

I have these lines, to exemplify this model: "xx.xxx.xxx/xxxx-xx" .....

"-CNPJ 03.501.509/0001-06."

"CNPJ-MF sob o n° 01.024.050/0001-71"

I need to return the following values:

03.501.509/0001-06

01.024.050/0001-71

Now example for this model: "xxx.xxx.xxx-xx" .....

I have the following lines:

"-CPF N°. 041.484.261-86"

"- Prefeito de Campo Grande - CPF 489.872.711-53."

I need to return the following values:

041484261-86

489872711-53

I hope I have explained ...

Thx...

Re: Filter

jonathandienst — Wed, 06 Aug 2014 14:15:22 GMT

If the numerical pattern you need is always the last part of the string, then:

=SubField(yourfield, ' ', -1) will work.

But your attached file does not contain those patterns as per your post. It is unstructured text which is very hard to read into an application such as QV which is designed to work with more structured data.

HTH

Jonathan

Re: Filter

Clever_Anjos — Wed, 06 Aug 2014 14:20:41 GMT

Hi Matheus,

You can start with this and fiinish the 'cleanup' process

LOAD

purgechar(@1,'ÁabcdefghijklmnopqrstuwxvyzABCDEFGHIJKLMNOPQRSTUWXVYZçÇãÃéÉíÍúÚ º,: êÊ´') as @1,

wildmatch(@1,'*??.???.???-??*')

FROM

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

where

wildmatch(@1,'*CNPJ*??.???.???/????-??*')

or wildmatch(@1,'*CNPJ-MF*??.???.???/????-??*')

or wildmatch(@1,'*CPF*??.???.???-??*');