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

[arnould_it]

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

[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

[arnould_it]

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

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 · Accepted solution]

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]

It works,

thanks!