Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
thakrarm
New Contributor II

Broken pipe delimiter encoding error

Hi,

I have been trying to load a simple file which uses a 'broken pipe' delimiter (¦).  This is the first time I've come across this type of delimiter and have had problems with it from the start.  The main issue is the introduction of an extra character in the data, which I can only assume has been added as part of the encoding process?

The sample qvw and data file is attached.  The file itself is being sent from a third party so I'm not able to change the delimiter or file at this stage.

Is there any way to load this data without gaining the extra character in the data?

Any help would be appreciated.

Thanks,

Mehul

Tags (3)
1 Solution

Accepted Solutions
pradeepreddy
Valued Contributor II

Re: Broken pipe delimiter encoding error

Try the bellow script....

as per your data, I assumed that there would be only 4 columns

Test:

Load

purgechar(@1,'Â') as "10PLANT",

purgechar(@2,'Â') as "20STOR_LOC",

purgechar(@3,'Â') as "30TXTMD",

purgechar(@4,'Â') as "40TXTMD"

from TEST.csv

(txt, utf8,  no labels,delimiter is '\xa6',header is 1 lines);

5 Replies
mwoolf
Honored Contributor II

Re: Broken pipe delimiter encoding error

I suggest loading the file without specifying the delimiter, so you get a single field.

Then use subfield to parse.

pradeepreddy
Valued Contributor II

Re: Broken pipe delimiter encoding error

Try the bellow script....

as per your data, I assumed that there would be only 4 columns

Test:

Load

purgechar(@1,'Â') as "10PLANT",

purgechar(@2,'Â') as "20STOR_LOC",

purgechar(@3,'Â') as "30TXTMD",

purgechar(@4,'Â') as "40TXTMD"

from TEST.csv

(txt, utf8,  no labels,delimiter is '\xa6',header is 1 lines);

Re: Broken pipe delimiter encoding error

Hi,

probably not the most straight forward solution but without hard coded field names or numbers:

QlikCommunity_Thread_210363_Pic1.JPG

tabHeaderTemp:

LOAD RecNo()&','&Replace(@1,'¦',',') as header

FROM [https://community.qlik.com/servlet/JiveServlet/download/1002119-217370/TEST.csv] (txt, utf8, no labels)

Where RecNo()=1;

tabLines:

LOAD RecNo()&','&Replace(@1,'¦',',') as line

FROM [https://community.qlik.com/servlet/JiveServlet/download/1002119-217370/TEST.csv] (txt, utf8, no labels)

Where RecNo()>1;

tabHeader:

CrossTable (HeadNum,HeadStr)

LOAD * FROM_FIELD ('tabHeaderTemp', header) (txt, utf8, no labels, delimiter is ',', msq);

mapHeader:

Mapping LOAD HeadNum, HeadStr Resident tabHeader;

DROP Tables tabHeaderTemp, tabHeader;

tabResult:

LOAD * FROM_FIELD ('tabLines', line) (txt, utf8, no labels, delimiter is ',', msq);

RENAME Fields using mapHeader;

DROP Field @1;

DROP Table tabLines;

hope this helps nevertheless

regards

Marco

thakrarm
New Contributor II

Re: Broken pipe delimiter encoding error

Hi Pradeep and Marco,


Thank you both for the solutions, they both work.  However, I'm going to go with the purge method.  Replacing the delimiter per row is fine on the test file but the main data file is quite large and this will increase the loading time.

Thanks again,

Mehul

Re: Broken pipe delimiter encoding error

Thanks for your feedback.

Regards

Marco

Community Browser