topic Re: for each loop in QlikView

for each loop

rajeshqvd — Mon, 28 May 2018 10:39:18 GMT

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

VBA: Count amount of letters and numbers with function

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:

Re: for each loop

marcus_sommer — Mon, 28 May 2018 14:10:29 GMT

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

Re: for each loop

rajeshqvd — Tue, 29 May 2018 07:46:55 GMT

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

Re: for each loop

marcus_sommer — Tue, 29 May 2018 08:37:44 GMT

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