Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator II
Creator II

for each loop

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:

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

View solution in original post

3 Replies
MVP & Luminary
MVP & Luminary

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

Creator II
Creator II

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

MVP & Luminary
MVP & Luminary

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

View solution in original post