Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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

Highlighted
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