Discussion board where members can get started with QlikView.
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 | )
'Value1 | Value2 | Value3' or
'Value1 | Value2 ' or
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= Value1Source2= Value2Source3= Value3
Line 2:Source1= Value1Source2= Value2Source3= empty
Line3:Source1= Value1Source2= emptySource3= 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 🙂
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 | Value3Value1 | Value 2|Value1
Expectation:For Line 1:Source1: Value1Source2: Value2Source3: Value3
For Line 2:Source1: Value1Source2: Value2Source3: empty
For Line 3:Source1: Value1Source2: emptySource: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
can you share a sample DATA for source and output expected ?