for each loop

[rajeshqvd]

Please help..... how can i do below requirement in qlik sense .

VBA: Count amount of letters and numbers with function

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Function AlphaNumeric(pInput As String) As String 'Updateby20140303 Dim xRegex As Object Dim xMc As Object Dim xM As Object Dim xOut As String Set xRegex = CreateObject("vbscript.regexp") xRegex.Global = True xRegex.ignorecase = True xRegex.Pattern = "[^\w]" AlphaNumeric = "" If Not xRegex.test(pInput) Then     xRegex.Pattern = "(\d+|[a-z]+)"     Set xMc = xRegex.Execute(pInput)     For Each xM In xMc         xOut = xOut & (xM.Length & IIf(IsNumeric(xM), "N", "L"))     Next     AlphaNumeric = xOut End If End Function

3. Save the code and close the window, and type this formula =AlphaNumeric(A1) ( the Cell A1 indicates the cell you want to count, you can change it as you need) into a blank cell, then press Enter and drag the fill handle to fill the range you want to use this formula. See screenshot:

[marcus_sommer]

AFAIK there is until now no regex implemented within Qlik. For QlikView there are also macro-approaches available but in Qlik Sense I don't know if there already any extensions for it - but you could also build your own maybe taking this as a starting point: JavaScript RegExp Reference

On the other side this is a rather simple case for a regex and could be also solved with a few lines of Qlik script, for example with the following approach:

MapNumber: mapping load chr(47 + recno()) as Number, 'X' as Replacement autogenerate 10;
MapString: mapping load chr(64 + recno()) as String, 'Y' as Replacement autogenerate 26;
MapReplaceNumber: mapping load repeat('X', recno()) as X, 'N' as Replacement autogenerate 10;
MapReplaceString: mapping load repeat('Y', recno()) as Y, 'L' as Replacement autogenerate 10;

table:
load *, rowno() as RowNo, len(F4) as FLen;
load *, applymap('MapReplaceNumber', F5, F5) as F6;
load *, applymap('MapReplaceString', F4, F4) as F5;
load *, iterno() as IterNo, subfield(F3, '#', iterno()) as F4 while iterno() <= substringcount(F3, '#') + 1;
load *, replace(replace(F2, 'XY', 'X#Y'), 'YX', 'Y#X') as F3;
load *, mapsubstring('MapNumber', mapsubstring('MapString', upper(F))) as F2;
load *, recno() as RecNo inline [
F
213guo
q4x13c
];

left join(table)
load RecNo, concat(FLen & F6, '', RowNo) as Final resident table group by RecNo;

which lead to a table like this:

In your final solutions you could skip just a few intermediate steps which are here just for checking the logic.

- Marcus

[rajeshqvd]

Thanks Marcus for quick response please do this for special characters also example 213/-guo as 3L2S3N

[marcus_sommer · Accepted solution]

To consider also special characters they need to be included within the 'MapString' mapping-table. For this you could extend the above used generic logic with a broader autogenerate number/index with or without some if-conditions or probably easier to concatenate multiple ones.

MapStringTemp:

load chr(64 + recno()) as String, 'Y' as Replacement autogenerate 26;

concatenate

load chr(44 + recno()) as String, 'Y' as Replacement autogenerate 3;

MapString: mapping load * resident MapStringTemp;

drop tables MapStringTemp;

As a simple alternatively you could create these tables in excel and use them as mapping-source. Here also an example to the ASCII code tables‌.

- Marcus