topic How to get multiple values between 'pipe' from a substring in QlikView

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

arnould_it — Sun, 11 Feb 2018 10:01:03 GMT

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

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

Anil_Babu_Samineni — Sun, 11 Feb 2018 12:08:57 GMT

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

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

arnould_it — Sun, 11 Feb 2018 17:07:21 GMT

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?

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

michal__ — Sun, 11 Feb 2018 18:08:07 GMT

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ł

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

Digvijay_Singh — Sun, 11 Feb 2018 19:51:34 GMT

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

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

arnould_it — Sun, 11 Feb 2018 21:05:18 GMT

It works,

thanks!