Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
in front end perhaps with a lot of nested replace
Replace(Replace(upper([Raw Data]), '1', 'n'),'2','n')
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
];
in front end perhaps with a lot of nested replace
Replace(Replace(upper([Raw Data]), '1', 'n'),'2','n')
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
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!