topic Replacing characters in a string in QlikView

Replacing characters in a string

Mon, 14 Nov 2011 13:41:35 GMT

I have sequence column with the following values : 3,1,1,0,4

I like to replace the numbers with characters

0=A

1=B

3=C

4=d

I manage to replace 1 character using the “replace”, but I didnot manage to replace the entire string with the information that I needed.

I will appreciate your help.

Replacing characters in a string

Mon, 14 Nov 2011 14:10:36 GMT

Hi tkalimi. Depends on what the field contains exactly.

Would values in that field only contain just one number as the whole value? EG

ID,Custom_Field

1, 3

2, 1

3, 1

4, 0

5, 4

If so, you could create a mapping table that maps those numbers to the letters specified. Alternately you could use a formula on the character number to change it to those numbers.The function to return the ascii number is Ord().

EG load ID, ord(Custom_Field) - 64 as new_field.....

ie ord('A') - 64 = 65-64 = 1

If your string is more complex than that, eg a series of concatenated numbers 31_4 or something like that you could use nested replace() functions

EG load ID, replace(replace('custom_field','1','A'),'2','B') as new_field...

Which one do you mean?

Erica.

Re: Replacing characters in a string

Mon, 14 Nov 2011 14:24:15 GMT

Thanks a lot,

I use the concatenated replace option.

Cheers,

Replacing characters in a string

Mon, 14 Nov 2011 14:34:29 GMT

You're welcome!

Regards, Erica