topic Re: Transformation Wizard Delimiter Issue in QlikView

Transformation Wizard Delimiter Issue

Tue, 17 Jun 2014 13:59:59 GMT

I have a text file that I am pulling through the transformation wizard using the delimiter '|' and embedded labels. There is one specific column in the data that is so long that it ends up wrapping down into a separate new row in the transformation process and shifting the remaining columns over. The issue isn't with the delimiter. The rows where that specific cell isn't that large pull through correctly. Does anyone have any ideas how to resolve this issue?

Re: Transformation Wizard Delimiter Issue

Gysbert_Wassenaar — Tue, 17 Jun 2014 14:41:03 GMT

That sounds like there are line breaks that should be there. Try to remove those from the source file so that every records is on one line only.

Re: Transformation Wizard Delimiter Issue

Tue, 17 Jun 2014 14:45:11 GMT

Perform a purge load before doing your cross table to load all the data into the correct fields. (Just a regular load and find out the proximate cause) You may need to remove carriage returns etc as mentioned by Gysbert

Re: Transformation Wizard Delimiter Issue

Tue, 17 Jun 2014 15:12:23 GMT

What if the line breaks are occurring due to length of line items in the file? The data is loaded out of a .txt file, and I think the issue might be due to exceeding the maximum characters per line in the file type. The only delimiter we have set in the script load is '|' so I'm confused why carriage returns would make any difference. It seems as if the load should ignore carriage returns and only break at the pipe delimiter.

Re: Transformation Wizard Delimiter Issue

Tue, 17 Jun 2014 15:15:47 GMT

Are you saying to do a normal load and then use the built in cross table function? We're not trying to transpose any data, we just want to correct the wrapping of a single string field.

Would it be possible to do a search for '\n' in the source file and delete all instances? Other than that, I can't figure out why the script would delimit on new line. The only delimiter we have set is '|' so I would assume it would ignore new lines/carriage returns.

Re: Transformation Wizard Delimiter Issue

Gysbert_Wassenaar — Tue, 17 Jun 2014 18:45:11 GMT

The delimiter is the field separator. A line break is the record separator. That's why a spurious line break can lead to the behaviour you're experiencing.