Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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
Employee
Employee

Re: How to Split a Numeric String

The separator is always IN ?

MVP
MVP

Re: How to Split a Numeric String

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)

Re: How to Split a Numeric String

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)

Re: How to Split a Numeric String

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

Community Browser