topic Re: Separate text to columns, when between the text it's the "|" caracter in QlikView

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

Fri, 22 Mar 2013 19:05:30 GMT

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

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

julian_rodriguez — Fri, 22 Mar 2013 19:20:32 GMT

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

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

Fri, 22 Mar 2013 19:30:07 GMT

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;

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

julian_rodriguez — Fri, 22 Mar 2013 19:40:28 GMT

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

Regards