topic Re: Function that deterministically transforms a text to a number? in QlikView

Function that deterministically transforms a text to a number?

julioarriaga — Wed, 25 Jul 2018 02:16:35 GMT

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.

Re: Function that deterministically transforms a text to a number?

Anonymous — Wed, 25 Jul 2018 05:37:29 GMT

Hi Julio

For that I would recommend the autonumber function.

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

Best regards,

Simon

Re: Function that deterministically transforms a text to a number?

Anonymous — Wed, 25 Jul 2018 06:09:22 GMT

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.

Re: Function that deterministically transforms a text to a number?

mdmukramali — Wed, 25 Jul 2018 06:41:08 GMT

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

];

find the attached sample application for the same,

Re: Function that deterministically transforms a text to a number?

BalaBhaskar_Qlik — Wed, 25 Jul 2018 07:07:08 GMT

Check this:

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

Re: Function that deterministically transforms a text to a number?

BalaBhaskar_Qlik — Wed, 25 Jul 2018 07:07:38 GMT

Apply Map on Substring

Re: Function that deterministically transforms a text to a number?

raviityou — Wed, 25 Jul 2018 08:44:06 GMT

hope this will you substring in qlikview?

Re: Function that deterministically transforms a text to a number?

julioarriaga — Thu, 26 Jul 2018 20:01:51 GMT

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

Re: Function that deterministically transforms a text to a number?

julioarriaga — Thu, 26 Jul 2018 20:03:17 GMT

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