Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Separate string value in three fields

Hi Community,
I'm confused finding the right commands in my DataModel to separate a data string.

From my Datasource I can load a string which is built up like this:
(Up to three Vales per line, separeted with a |  )

example:

'Value1 | Value2 | Value3'  or 

'Value1 | Value2 ' or

'Value1'

Now I can load this string into a field named Source for example.
But I also want up to three fields (Source 1-3) which are filled with the values which are separated with |

From my Example above:

Line 1:
Source1= Value1
Source2= Value2
Source3= Value3

Line 2:
Source1= Value1
Source2= Value2
Source3= empty

Line3:
Source1= Value1
Source2= empty
Source3= empty

So, how can I separate this string to get values which can be filled into Source1-3?
I think the "problem" is, that you can have up to three variants if the fields Source2-3 are filled or not.

Until now I've tried:

SubField(Source,'|',1) as Source1,
SubField(Source,'|',2) as Source2,
mid(Source,index(Source,'|',2)+1) as Source3,

 

 

This works good for example 1 with 3 values to be filled but not 2 and three 😞

 

Hope anybody can give me some advice 🙂

 

 

1 Solution

Accepted Solutions
Contributor II
Contributor II

Thanks for your reply.I have already found a solution.
To complete the thread I will give some example Data and the solution which worked for me:

Source:
Value1 | Value2 | Value3
Value1 | Value 2|
Value1

Expectation:
For Line 1:
Source1: Value1
Source2: Value2
Source3: Value3

For Line 2:
Source1: Value1
Source2: Value2
Source3: empty

For Line 3:
Source1: Value1
Source2: empty
Source:3 empty

 

And here is my solution:
For Field Source 1:
SubField(Source,'|',1) as Source1,

For Field Source 2:
SubField(Source,'|',2) as Source2,

For Field Source 3:
TextBetween(Source,' | ','',2)as Source3,

 

 

 

 

View solution in original post

2 Replies
Master II
Master II

Hi,

can you share a sample DATA for source and output expected ?

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
Contributor II
Contributor II

Thanks for your reply.I have already found a solution.
To complete the thread I will give some example Data and the solution which worked for me:

Source:
Value1 | Value2 | Value3
Value1 | Value 2|
Value1

Expectation:
For Line 1:
Source1: Value1
Source2: Value2
Source3: Value3

For Line 2:
Source1: Value1
Source2: Value2
Source3: empty

For Line 3:
Source1: Value1
Source2: empty
Source:3 empty

 

And here is my solution:
For Field Source 1:
SubField(Source,'|',1) as Source1,

For Field Source 2:
SubField(Source,'|',2) as Source2,

For Field Source 3:
TextBetween(Source,' | ','',2)as Source3,

 

 

 

 

View solution in original post