Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Separate string into two fields with delimiter

Hi guys,

I'm trying to separate a field into 2 different fields where my delimiter is '-' although there are a bunch of them on the text.

I tryied the SubField function but it gets only the string before the next delimiter. For eg.

My text is (delimiter in bold red):

G820 - CNHi Comercio de Pecas Ltda-TRADE RECEIVABLES NACIONAL-ALL PC-ALL TP-GRP----0L--

Trying to get:

G820 - CNHi Comercio de Pecas Ltda

and

TRADE RECEIVABLES NACIONAL-ALL PC-ALL TP-GRP


SubField(Myfield,3) gets only 'TRADE RECEIVABLES NACIONAL'


Note that the first bit of the string and the last vary, so I cannot use the Index function searching for a string.


Thanks!

1 Solution

Accepted Solutions
marcus_sommer

Some kind of logic will be needed. If it's always the second '-' then you could use:

mid(Field, 1, index(Field, '-', 2) - 1)

and

mid(Field, index(Field, '-', 2) + 1, 100)

- Marcus

View solution in original post

4 Replies
richard_chilvers
Specialist
Specialist

Hi Claudio

Its hard to answer without knowing exactly how your data is formatted.

Will the left 7 characters always be 'Annn - ' where A is an alpha and n are numeric digits ?

If so, this might make life easier because you can ignore these first 7 character (including the first '-') when you analyse the string.

marcus_sommer

Some kind of logic will be needed. If it's always the second '-' then you could use:

mid(Field, 1, index(Field, '-', 2) - 1)

and

mid(Field, index(Field, '-', 2) + 1, 100)

- Marcus

Anonymous
Not applicable
Author

Richard, the text before and after the delimiter I need varies in lenght, so I can't ignore measure how many characters to ignore. Thanks though!

Anonymous
Not applicable
Author

Great approach, Marcus! Thanks!