Skip to main content
Woohoo! Qlik Community has won “Best in Class Community” in the 2024 Khoros Kudos awards!
Announcements
Nov. 20th, Qlik Insider - Lakehouses: Driving the Future of Data & AI - PICK A SESSION
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

How to Split a Numeric String

Hello,

I have a key in the datamodel that is a combination of two other key. I need to separate this key into two that it was made combining with. ex 123456IN684123 is the key made using  key 123456 and key IN684123. please help me with this.

Thank You,

Syed.

4 Replies
Clever_Anjos
Employee
Employee

The separator is always IN ?

maxgro
MVP
MVP

try with subfield if you always have the same separator

=SubField('123456IN684123', 'IN', 1)

=SubField('123456IN684123', 'IN', 2)

or

=right('123456IN684123',  len('123456IN684123') - index('123456IN684123', 'IN') +1)

sunny_talwar

You can also try this:

1st Part -> Numeric part before any text

=Left('123456IN684123', FindOneOf('123456IN684123', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')-1)

or more generally

Left(KeyField, FindOneOf(KeyField, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')-1)


2nd Part -> Rest of the key

=Right('123456IN684123', Len('123456IN684123') - FindOneOf('123456IN684123', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')+1)

or more generally

Right(KeyField, Len(KeyField) - FindOneOf(KeyField, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ')+1)

MarcoWedel

Hi,

quite similar a solution using any non numeric character as key delimiter:

QlikCommunity_Thread_228128_Pic1.JPG

LOAD *,

    Left(Key,FindOneOf(Key,PurgeChar(Key,'0123456789'))-1) as Key1,

    Mid (Key,FindOneOf(Key,PurgeChar(Key,'0123456789')))  as Key2

Inline [

Key

123456IN684123

12345A678

1234BCD67

123EF456789

12345678@9012345

];

hope this helps

regards

Marco