Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
nico_v_c
New Contributor

How to split a field in different new fields

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

1 Solution

Accepted Solutions
tomasz_tru
Valued Contributor

Re: How to split a field in different new fields

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

5 Replies
MVP
MVP

Re: How to split a field in different new fields

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



sergio0592
Valued Contributor II

Re: How to split a field in different new fields

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)

tomasz_tru
Valued Contributor

Re: How to split a field in different new fields

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

nico_v_c
New Contributor

Re: How to split a field in different new fields

Thx Tomasz,

It works fine

Nico   

oknotsen
Honored Contributor III

Re: How to split a field in different new fields

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 .

May you live in interesting times!
Community Browser