topic Re: Pattern Matching in QlikView

Pattern Matching

manas_bn — Mon, 02 Jun 2014 07:48:39 GMT

Hello all,

I am trying to build an application to check patterns of a few fields in the data. The pattern should be built with 'n' for numbers and 'a' for alphabets. For ex: if the data has '123 A' as value, it's pattern will be 'nnn a' Here is an example of what I need.

Raw data	Straight table (RESULT)
1234	Pattern	Count
2345	nnnn	2
123-ABC	nnn-aaa	1
12 BA	nn aa	1

The raw data should remain as it is and the pattern matching needs to happen in the front end, not in the script editor.

As of now I am lost with this and need a nudge in the right direction. Please help!

Cheers!

Re: Pattern Matching

tresB — Mon, 02 Jun 2014 08:34:27 GMT

I guess, you have to do it in the script, like:

Map:
Mapping Load * Inline [
A, B
1, n
2, n
3, n
4, n
5, n

A, a
B, a
C, a
];

Load *, MapSubString('Map',Data ) as Patterned Inline [
Data
1234
2345
123_ABC
123-AB
];

Re: Pattern Matching

maxgro — Mon, 02 Jun 2014 09:13:56 GMT

in front end perhaps with a lot of nested replace

Replace(Replace(upper([Raw Data]), '1', 'n'),'2','n')

Re: Pattern Matching

ThornOfCrowns — Mon, 02 Jun 2014 09:27:46 GMT

You can use RegEx in the load script which might suit your needs. For a full explanation, see the following:

www.

qlikfix.com/2010/10/18/regular-expressions-in-the-load-script/#more-155

Re: Pattern Matching

manas_bn — Mon, 02 Jun 2014 21:54:37 GMT

Thanks to everyone!

I ended up using Mossimo's solution. The mapping table and Regexp were great suggestions, but I needed something in the front end.

Here is the expression I used:

Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(
upper([Raw Data])), '1', 'n'),'2','n'),3,'n'),4,'n'),5,'n'),6,'n'),7,'n'),8,'n'),9,'n'),0,'n'),'A','a'),'B','a'),'C','a'),'D','a'),'E','a'),'F','a'),'G','a'),'H','a'),'I','a'),'J','a'),'K','a'),'L','a')
,'M','a'),'N','a'),'O','a'),'P','a'),'Q','a'),'R','a'),'S','a'),'T','a'),'U','a'),'V','a'),'W','a'),'X','a'),'Y','a'),'Z','a')

Cheers!