Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arnould_it
Contributor III
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

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 ];

View solution in original post

5 Replies
Anil_Babu_Samineni

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
arnould_it
Contributor III
Contributor III
Author

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__
Contributor III
Contributor III

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

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
Contributor III
Contributor III
Author

It works,

thanks!