Qlik sense - Identify sequential alphabets or numerics

[Sush123]

Hi , 

I need a help for the below scenario where need to identify the presence of Sequential Alphabets or numerics( Min 3 chars) in a string which will be considered as an error.

Ex: 1)ABC23455467MIOQ

     2)145649744XYZP

    3)OXPTR21598

In the above examples, 1) and 2) are considered as errors as there are presence of sequential chars.

Kindly suggest any ideas to check this in Qlik sense.

[maxgro · Accepted solution]

An idea could be to use a variable with all strings to be considered errors (sequential numeric or alpahebetic of 3 chars)

and the wildmatch function.

// define the string with sequential chars

SET v = '*012*', '*123*', '*234*', '*345*', '*456*', '*567*', '*678*', '*789*', '*890*';
SET v = $(v) , '*ABC*', '*BCD*';     // add the others

// test data

t:
load * inline [
col1
ABC23455467MIOQ
145649744XYZP
OXPTR21598
ZXDABC1A1A
abc
];

// check for error
t2:
load
col1,
wildmatch(col1, $(v)) > 0 as ERROR
Resident t;

[marcus_sommer]

Similar from the logic but slightly different from the methods is the following which creates the sequential chars within a mapping table and makes the evaluating per mapsubstring() + textbetween():

m:
mapping load chr(47 + recno()) & chr(47 + recno() + 1) & chr(47 + recno() + 2) as [Lookup],
   '<<invalid>>' as [Return] autogenerate 8;
mapping load chr(64 + recno()) & chr(64 + recno() + 1) & chr(64 + recno() + 2) as [Lookup],
   '<<invalid>>' as [Return] autogenerate 24;
mapping load chr(96 + recno()) & chr(96 + recno() + 1) & chr(96 + recno() + 2) as [Lookup],
   '<<invalid>>' as [Return] autogenerate 24;

t:
load *, if(len(C1), C1, 'valid') as C2;
load *, textbetween(mapsubstring('m', F), '<<', '>>') as C1;
load * inline [
F
ABC23455467MIOQ
145649744XYZP
OXPTR21598
];

- Marcus

[Sush123]

Thank you so much for the above logics, but now there is slight change in the requirement. I need to find the string which is having full length of sequential chars .

Ex: ABCDEFG, 12345678, PQRSTUVW etc.

[Sush123]

Also the length of the string is not of constant length. It may vary.

[marcus_sommer]

The above applied approach could be extended to create dynamically mapping-information within a more or less automated loop - not only to identify certain use-cases else also to return differentiated results. The following is not a complete solution for all your possibilities/requirements - and you may need some playing with it to adjust it to the exact scenarios - but it enables you to integrate most of them. You may need some more iterations and following filters for the final mapping load and within the origin load some checks for the length or similar measures:

for each type in 'num', 'upper', 'lower'
   let i = pick(match('$(type)', 'num', 'upper', 'lower'), 8, 24, 24);
   let start = pick(match('$(type)', 'num', 'upper', 'lower'), 46, 63, 95);
   m:
   load *, pick(IterNo, 'single', 'double', 'triple', 'quadruple', 'quintuple') as Return;
   load *, if(RecNo = previous(RecNo), peek('Lookup') & Char, Char) as Lookup;
   load recno() as RecNo, iterno() as IterNo, chr($(start) + recno() + iterno()) as [Char],
             '$(type)' as Type
   autogenerate $(i)
   while iterno() <=5 and $(start) + recno() + iterno() <= $(start) + $(i) + 5 - 2;
next

Just execute it within a dummy-app and play with it until it fulfilled the requirements.

- Marcus    

[MarcoWedel · Accepted solution]

Hi,

one solution might be:

tabConsecChar:
LOAD String,
     If(Max(IsConsecutive),'Error','Non Error') as Error
Group By String;
LOAD *,
     If(IterNo()>1,Ord(Mid(String,IterNo()))-Ord(Mid(String,IterNo()-1))=1) as IsConsecutive
INLINE [
    String
    ABC23455467MIOQ
    145649744XYZP
    OXPTR21598
    AB
    ABC
    ABD
    ABCD
    ABCDE
    ABCDEF
    ABCDEG
    ABCDEFG
    ABCDEFH
    HIJ
    HIK
    HIJK
    HIKJ
    12345678
    345
    346
    3456
    3457
    34567
    34568
    345678
    PQRSTUVW
]
While IterNo()<=Len(String);

hope this helps

Marco

[maxgro · Accepted solution]

may be

set v2 = 'ABCDEFGHILMNOPQRSTUVZ-0123456789';

x:
load * inline [
String
ABC23455467MIOQ
145649744XYZP
OXPTR21598
AB
ABC
ABD
ABCD
ABCDE
ABCDEF
ABCDEG
ABCDEFG
ABCDEFH
HIJ
HIK
HIJK
HIKJ
12345678
345
346
3456
3457
34567
34568
345678
PQRSTUVW
UVZ012
];

x2:
load
String,
index('$(v2)', String) > 0 as Error
Resident x;

[Sush123]

Hi,

Thanks for the nice solution. This will work.

[Sush123]

Hi @MarcoWedel , 

This is such a great solution. Thanks for your help.