Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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
If you share sample data will be more easy to help then please provide in any inline table.
Done !
load the file into QlikView and then use wildmatch function
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
Hi Matheus,
Try using Wilmatch
IF(WILDMATCH(@1, '*CNPJ*'),@1) AS [CNPJ:Filtered]
see the attached file based on your txt file
Regards
Alan
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);
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
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
up!