topic Re: Qlik sense - Identify sequential alphabets or numerics in App Development

Qlik sense - Identify sequential alphabets or numerics

Sush123 — Tue, 30 Nov 2021 16:25:32 GMT

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.

Re: Qlik sense - Identify sequential alphabets or numerics

maxgro — Sun, 28 Nov 2021 19:03:41 GMT

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;

Re: Qlik sense - Identify sequential alphabets or numerics

marcus_sommer — Mon, 29 Nov 2021 10:37:59 GMT

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

Re: Qlik sense - Identify sequential alphabets or numerics

Sush123 — Mon, 29 Nov 2021 11:22:29 GMT

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.

Re: Qlik sense - Identify sequential alphabets or numerics

Sush123 — Mon, 29 Nov 2021 11:23:47 GMT

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

Re: Qlik sense - Identify sequential alphabets or numerics

marcus_sommer — Mon, 29 Nov 2021 14:36:55 GMT

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

Re: Qlik sense - Identify sequential alphabets or numerics

MarcoWedel — Mon, 29 Nov 2021 19:16:27 GMT

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

Re: Qlik sense - Identify sequential alphabets or numerics

maxgro — Mon, 29 Nov 2021 20:20:50 GMT

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;

Re: Qlik sense - Identify sequential alphabets or numerics

Sush123 — Tue, 30 Nov 2021 05:13:38 GMT

Hi,

Thanks for the nice solution. This will work.

Re: Qlik sense - Identify sequential alphabets or numerics

Sush123 — Tue, 30 Nov 2021 05:15:22 GMT

Hi @MarcoWedel ,

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