CR/LF Issue

[datagrrl]

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.

[maxgro]

could you post the excel?

[datagrrl]

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

[petter · Accepted solution]

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

[datagrrl]

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.

[petter]

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

[datagrrl]

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.

[flipside]

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

[petter]

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...