topic Re: How to Split a Numeric String in QlikView

How to Split a Numeric String

Fri, 05 Aug 2016 19:51:39 GMT

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.

Re: How to Split a Numeric String

Clever_Anjos — Fri, 05 Aug 2016 21:24:01 GMT

The separator is always IN ?

Re: How to Split a Numeric String

maxgro — Fri, 05 Aug 2016 21:32:21 GMT

try with subfield if you always have the same separator

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

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

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

Re: How to Split a Numeric String

sunny_talwar — Sun, 07 Aug 2016 05:14:39 GMT

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

MarcoWedel — Mon, 08 Aug 2016 23:30:04 GMT

Hi,

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

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