topic Re: CR/LF Issue in QlikView

CR/LF Issue

datagrrl — Thu, 17 Sep 2015 21:31:22 GMT

Hello-

I have a pull from an Excel sheet. The Main Key field for the app if an alphanumeric field. For formatting reasons some of the cells with this key have a CR/LF in them.

I can strip this out in a Qlik expression and it works well:

Replace(MyFieldName,'

',' ')

It doesn't work as well in the Script Editor, which is where I really need it to work, since it is a key field.

Have you seen this before, and how have dealt with it?

I am going to try and fix this upstream in the ETL, but I thought I would ask here first.

Re: CR/LF Issue

maxgro — Thu, 17 Sep 2015 21:50:51 GMT

could you post the excel?

Re: CR/LF Issue

datagrrl — Thu, 17 Sep 2015 22:04:27 GMT

The next step after loading is a Crosstable, in case that explained anything.

Re: CR/LF Issue

petter — Fri, 18 Sep 2015 03:40:30 GMT

Actually the CROSSTABLE(...) LOAD seems to handle the CR LFs fine. So getting rid of them and replacing them with single space could be at a following step like you see below:

The only thing to be aware of is that the CR LFs are now considered a part of the field name and as such you will have problems with your load script if they are not consistent between subsequent reloads. If they are not a slightly more sophisticated code is needed to handle that scenario...

Hope this helps...

- petter

Re: CR/LF Issue

datagrrl — Fri, 18 Sep 2015 10:32:19 GMT

Okay, I think your method of removing the characters is better than me trying to paste the character. Can't believe I didn't think of that. Thanks.

Re: CR/LF Issue

petter — Fri, 18 Sep 2015 11:24:15 GMT

Glad to help. Could you please close the thread by marking it as answered?

Re: CR/LF Issue

datagrrl — Fri, 18 Sep 2015 11:53:32 GMT

I had to get to work before I knew if it solved my issue. It didn't work for some reason, but there is probably soemthing else wrong with the data that I haven't seen yet.

Re: CR/LF Issue

flipside — Fri, 18 Sep 2015 12:44:39 GMT

That's a nice method.

One issue I've had with this though is, as you pointed out, ANY change to the field renders it unrecognisable. Even if the code just gets outdented when tidying up, it will probably cause a fail.

My alternative is to read the header line as data and create a mapping code line, then load the data and apply the mapping. This uses a subroutine which can be called at any point ...

Sub MapFields(tbl)
let vMap = '';

For i = 1 to NoOfFields('$(tbl)')
let vMap = '$(vMap)' & if($(i)>1,',') & FieldName($(i),'$(tbl)') & ' to [' & replace(replace(peek(FieldName($(i),'$(tbl)')),chr(10),''),chr(13),'') & ']';
Next i;
Drop Table '$(tbl)';
end sub;

Headers: //Load first excel line as data (ie no header)
First 1 LOAD A, B, C, D FROM [BadData.xlsx] (ooxml, no labels, table is Sheet1);
Call MapFields('Headers');

Data: //Load excel without the headers
LOAD A, B, C, D FROM [BadData.xlsx](ooxml, no labels, header is 1 lines, table is Sheet1);

Rename Fields $(vMap);

flipside

Re: CR/LF Issue

petter — Fri, 18 Sep 2015 13:08:17 GMT

Yes you are right - your approach is more robust and is what I would probably go for in any solution I would put into production...