Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Separate text to columns, when between the text it's the "|" caracter

Hey everyone, I have this question: I have a column that shows something like this:

|BASE Phenom 100||BASE Legacy 600||BASE Legacy 650||EASA (EU)||CAA (Aruba)||CAA (Cayman)||CAA (Isle of Man)||GCAA (UAE)||SAA (Ukraine)||RFATA (Russia)|

|BASE Phenom 100||BASE Phenom 300||BASE Legacy 600||BASE Legacy 650||FAA (USA)|

|LINE Phenom 300||BASE Phenom 100||ANAC (Brazil)|

Each line is a single cell (Excel cell).

So, what it's shown between the "||" I would like it to be displayed in separated cells. The file it is shown as "Delimited" when I ask to be opened.

On Excel, it would be something like "text to columns". I want to do it to those cells (they are in the same column) in the Qlikview.

Since now, I appreciate =]

1 Solution

Accepted Solutions
Not applicable
Author

If the delimiter file load doesn't work, you could use SubField():

Trial:

LOAD A as Test
FROM
YourFile.xls
(ooxml, no labels, table is Sheet1);

New:
LOAD
SubField(Test,'|',1) as A,
SubField(Test,'|',2) as B,
SubField(Test,'|',3) as C,
SubField(Test,'|',4) as D
Resident Trial;
DROP Table Trial;     

View solution in original post

3 Replies
julian_rodriguez
Partner - Specialist
Partner - Specialist

You can change the delimiter parameter, on the load... something like this:

LOAD

     Field1,

     Field2,

     Field3,

     Field4

FROM

test.txt //Your text or excel file

(txt, codepage is 1252, embedded labels, delimiter is '|', msq);

Hope this helps

Not applicable
Author

If the delimiter file load doesn't work, you could use SubField():

Trial:

LOAD A as Test
FROM
YourFile.xls
(ooxml, no labels, table is Sheet1);

New:
LOAD
SubField(Test,'|',1) as A,
SubField(Test,'|',2) as B,
SubField(Test,'|',3) as C,
SubField(Test,'|',4) as D
Resident Trial;
DROP Table Trial;     

julian_rodriguez
Partner - Specialist
Partner - Specialist

Yeah, you are right.. I didn't realize that it's an Excel file...

Regards