Function that deterministically transforms a text to a number?

[julioarriaga]

Helo,

I'm looking to know if there's a function that does something like this:

Input  -> Output

ABC  -> 123

AND  -> 1144

BTIO -> 220915

Thanks in advance.

[Anonymous]

Hi Julio

For that I would recommend the autonumber function.

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/CounterFunctio...

Best regards,

Simon

[Anonymous]

Well there is an ord() functioncn, but it only interprets the first character, so you have to use it several times for the first, the second, the third etc. character.

I used it to sort alphanumeric values in a chart.

[mdmukramali · Accepted solution]

Hi,

you can use the MapSubstring Function to convert alphabetic letters to numbers

here is the sample :

CharMap:

Mapping LOAD * Inline

[

Text,Number

A,1

B,2

C,3

D,4

E,5

F,6

G,7

H,8

I,9

J,10

K,11

L,12

M,13

N,14

O,15

P,16

Q,17

R,18

S,19

T,20

U,21

V,22

W,23

X,24

Y,25

Z,26

]

;

Data:

LOAD *,

MapSubString('CharMap', Input) AS Output

INLINE [

Input

ABC

AND

BTIO

AAA

ZA

];

find the attached sample application for the same,

[balabhaskarqlik]

Check this:

https://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/Scripting/MappingFunctio...

[balabhaskarqlik]

Apply Map on Substring

[raviityou]

hope this will you substring in qlikview?

[julioarriaga]

I'm afraid that function does a different thing as it assigns a number as the the script is reading the table.

[julioarriaga]

Thanks for the solution proposal, but I think the downside is that the script ends quite convoluted, doesn't it?