topic Re: Separate string value in three fields in QlikView

Separate string value in three fields

Fluppie — Wed, 25 Nov 2020 16:16:04 GMT

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 🙂

Re: Separate string value in three fields

Taoufiq_Zarra — Tue, 07 Jan 2020 14:42:15 GMT

Hi,

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

Re: Separate string value in three fields

Fluppie — Tue, 07 Jan 2020 14:49:03 GMT

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,