Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
The separator is always IN ?
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)
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)
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