Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

arnould_it
New Contributor III

How to get multiple values between 'pipe' from a substring

Hello everyone,

could anyone help me in this?

I've to get multiple values from a .dat file in which any record has a filed containing 4 values (sub-fields), separated by a pipe ('|'); any value can have 1, 2 or more characters; for example:

record 1, field 1  -      xxxxx|0|12|2|453|xxxxxxx

record 2, field 1  -      xxxxx|23|401|2|121|xxxxxx

Any value between pipes have to become a field in Qlik (value1, value2, value3, value4).

Which is the best way to solve this?

Thanks in advance,

Alessandro

1 Solution

Accepted Solutions
Digvijay_Singh
Honored Contributor III

Re: How to get multiple values between 'pipe' from a substring

Try this -

Load

SubField(Input,'|',2) as Field1,

SubField(Input,'|',3) as Field2,

SubField(Input,'|',4) as Field3,

SubField(Input,'|',5) as Field4

;

Load * inline [

Input

xxxxx|0|12|2|453|xxxxxxx

xxxxx|23|401|2|121|xxxxxx ];

5 Replies

Re: How to get multiple values between 'pipe' from a substring

How you want to split? Do you have output for given data? You mean this?

Field1, Value1, Value2, Value3, Value4, Field2

xxxxx, 0, 12, 2, 453, xxxxxxx

xxxxx, 23, 401, 2, 121, xxxxxx

Life is so rich, and we need to respect to the life !!!
arnould_it
New Contributor III

Re: How to get multiple values between 'pipe' from a substring

yes, it's correct:

Field1, Value1, Value2, Value3, Value4, Field2

xxxxx, 0, 12, 2, 453, xxxxxxx

xxxxx, 23, 401, 2, 121, xxxxxx

--

So, which is the best way to exactly capture values having different length (text format) between pipes?

michal__
New Contributor II

Re: How to get multiple values between 'pipe' from a substring

Hi,

I suggest you to take a look at SubField function.

SubField(text, delimiter[, field_no ])


If you know position of each substring, you can sinply:

[tableName]:

load

      SubField([yourFieldName], '|', 1)     as [Field 1]

     ,SubField([yourFieldName], '|', 2)     as [Field 2]

etc...


Regards,

Michał


Digvijay_Singh
Honored Contributor III

Re: How to get multiple values between 'pipe' from a substring

Try this -

Load

SubField(Input,'|',2) as Field1,

SubField(Input,'|',3) as Field2,

SubField(Input,'|',4) as Field3,

SubField(Input,'|',5) as Field4

;

Load * inline [

Input

xxxxx|0|12|2|453|xxxxxxx

xxxxx|23|401|2|121|xxxxxx ];

arnould_it
New Contributor III

Re: How to get multiple values between 'pipe' from a substring

It works,

thanks!