Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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
Highlighted
Master
Master

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") 😉
Highlighted
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