Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
smiling_cheetah
Contributor

Parse string based on Capitalize

Hi everyone, 

I come across the following task and would appreciate any help

 

So there is a single field for name and surname, and value look like that:

Load * inline [
NameSurname
JohnDow
JaneDow
MarySue
];

Is there any approach I can use to separate the values based on the capital letter? (both surname and name start with a capital)

 

Thank you in advance

Labels (2)
1 Solution

Accepted Solutions

Re: Parse string based on Capitalize

Try this

MappingLoad:
Mapping
LOAD Chr(64 + IterNo()) as MapFrom,
	 ' ' & Chr(64 + IterNo()) as MapTo
AutoGenerate 1
While (64 + IterNo()) <= 90;

LOAD NameSurname,
	 Trim(MapSubString('MappingLoad', NameSurname)) as Formated;
LOAD * INLINE [
    NameSurname
    JohnDow
    JaneDow
    MarySue
];
7 Replies

Re: Parse string based on Capitalize

Try this

MappingLoad:
Mapping
LOAD Chr(64 + IterNo()) as MapFrom,
	 ' ' & Chr(64 + IterNo()) as MapTo
AutoGenerate 1
While (64 + IterNo()) <= 90;

LOAD NameSurname,
	 Trim(MapSubString('MappingLoad', NameSurname)) as Formated;
LOAD * INLINE [
    NameSurname
    JohnDow
    JaneDow
    MarySue
];
Partner
Partner

Re: Parse string based on Capitalize

If i udnerstand you correctly upper case is the indicator

try this

FindOneOf('MariesSue','ABCDEFGHIJKLMONOPQRSTUVWXYZ',2)

this would return the position of the 2nd capital letter

So

first name would be

Left('MariesSue',
FindOneOf('MariedSue','ABCDEFGHIJKLMONOPQRSTUVWXYZ',2)-1
)

last name -

right('MariesSue',
Len('MariesSue')-FindOneOf('MariesSue','ABCDEFGHIJKLMONOPQRSTUVWXYZ',2)+1
)

smiling_cheetah
Contributor

Re: Parse string based on Capitalize

Wow, that's actually brilliant! 

You iterate through all capital letters to create a mapping with a space. 

The one thing I didn't mention is that those names are not in English, but in Ukrainian, so look like 

Load * inline [
ИмяФамилия
ДжонДоу
ДжейнДоу
МериСью
];

Is there an option to address Ukrainian letters through chr() ? 

And thanks!

Re: Parse string based on Capitalize

Check for yourself... I just checked =Ord('И') and it gives me 1048 and =Ord('Д') gives me 1044... you just need to know the range for your letters and use that.

 

smiling_cheetah
Contributor

Re: Parse string based on Capitalize

Hi, thanks, this looks great and is language-independent 

I guess this approach will work for me, really appreciate help

smiling_cheetah
Contributor

Re: Parse string based on Capitalize

Oh, nice, thanks!
Didn't knew of this function "ord()"

Re: Parse string based on Capitalize

Ya, that one basically is the opposite of Chr()... tells you which number to use with Chr() to find the character 🙂