topic Separate string into two fields with delimiter in QlikView

Separate string into two fields with delimiter

Anonymous — Wed, 17 May 2017 12:48:52 GMT

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!

richard_chilvers — Wed, 17 May 2017 13:08:19 GMT

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 — Wed, 17 May 2017 13:09:14 GMT

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 — Wed, 17 May 2017 13:16:05 GMT

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 — Wed, 17 May 2017 13:26:04 GMT

Great approach, Marcus! Thanks!