topic Re: How to split a field in different new fields in QlikView

How to split a field in different new fields

Anonymous — Thu, 26 Oct 2017 20:58:06 GMT

Hi,

I want to create different new fields using one basic field extract.

The basic field extract is someting like "035656AALKDMPRVLG"

where 35656 is the account code (leaving the null)

AAL is the Team

KDM is the CRM

PRVLG is the segment

Can anyone help me to get the right formula

Thanks in advance

Nico

Re: How to split a field in different new fields

swuehl — Thu, 26 Oct 2017 21:43:56 GMT

You can use string functions to separate the field value into parts.

Something using

LOAD

num(Keepchar(FIELD,'0123456789') ) as [Account code],

Left(Purgechar(FIELD,'0123456789'),3) as Team,

Mid(Purgechar(FIELD,'0123456789'),4,3) as CRM,

Mid(Purgechar(FIELD,'0123456789'),7,5) as segment

FROM ...;

or similar. Check out the string functions or explain all rules that define your combined field value (e.g. fixed length parts?).

String functions ‒ QlikView

Re: How to split a field in different new fields

sergio0592 — Thu, 26 Oct 2017 21:48:34 GMT

If these fields are always on the same number of digit, you can try with mid. Otherwise, it will be difficult whithout separator.

account code : mid(1,6)

Team : mid(7,3)

CRM : mid(10,3)

segment mid(13,5)

Re: How to split a field in different new fields

tomasz_tru — Thu, 26 Oct 2017 21:49:21 GMT

Are the fields alvays the same length? Right and Mid functionctions could be handy here:

Right(text,5) AS segment,

Mid(text,7,3) AS Team,

Mid(text,10,3) AS CRM,

num(Left(text,6)) AS account_code

Tomasz

Re: How to split a field in different new fields

Anonymous — Fri, 27 Oct 2017 08:25:35 GMT

Thx Tomasz,

It works fine

Nico

Re: How to split a field in different new fields

oknotsen — Fri, 27 Oct 2017 08:26:58 GMT

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post; not visible in preview) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .