topic Field name with carriage return in QlikView

Field name with carriage return

Fri, 29 Oct 2010 14:17:10 GMT

I am importing about 100 Excel spreadsheets. Some of the field names have carriage returns in them. I can import them with an "*" but how do you reference the field name in script? They work fine in objects. Is there a way of referencing them by field number, for example? There is also some inconsistancy in the worksheet names. Can you you reference worksheets by their sheet number?

Vous pouvez me répondre en français si vous êtes plus à l'aise.

Field name with carriage return

boorgura — Fri, 29 Oct 2010 14:32:47 GMT

Yes you can reference the fields by numbers.

At load, you can change the "embedded labels" to "explicit" and then change header size to 1 so that it will not read the header names as data.

But, I am not sure if we can refer the sheets by numbers.

Field name with carriage return

Fri, 29 Oct 2010 14:52:19 GMT

Thanks Rocky,

This definitely answers the first part of my question. I've haven't worked much with Excel files. I would still like to know how to reference worksheets generically.

Field name with carriage return

Fri, 29 Oct 2010 19:52:29 GMT

I spoke too soon. It turns out that the location of the columns is inconsistant as well. I can't use explicit fields names. At least the field names used seems to be consistant. If l load with an "*" and concatenate everything, at least the fields line up.

There must be someway of refering to a field by it's number. I've tried FieldName(16,'Projects') by this only returns the name of the field and not the contensts.

Field name with carriage return

Miguel_Angel_Baeyens — Fri, 29 Oct 2010 20:10:47 GMT

Hello Kevin,

You can use

LOAD @1 AS Field1, @2 AS Field2FROM [File.xls]WHERE Left(@3, 2) = 'AA'

That will use first, second and third columns of the selected sheet.

Hope that helps

Field name with carriage return

Fri, 29 Oct 2010 20:32:02 GMT

That would work if all the columns where in the same location. The problem I have is that I have hundreds of Excel files and at some point an extra column or 2 was added. If I "concatenate load" using the existing field names it combines the fields with the same name regarless of the column number.

The problem field has 2 carriage returns in it. Is there a way of refering to a field with a carriage return in it? I can dump the field in an object and it works just fine. I would like to be able to refer to the same field in a "Load Resident" statement.

Field name with carriage return

Miguel_Angel_Baeyens — Fri, 29 Oct 2010 20:36:40 GMT

Hi Kevin,

I'm likely missing something here. You can load (as the Table Files dialog will show) a field with carriage returns with something like

LOAD Field1, [Field With CRs] AS SomeOtherNameFROM ...

Hope that helps

Field name with carriage return

Fri, 29 Oct 2010 20:51:12 GMT

Thanks Miguel,

I've tried that and I'm getting a field not found error on the field with the carriage returns.

Kevin

Field name with carriage return

Miguel_Angel_Baeyens — Fri, 29 Oct 2010 20:57:35 GMT

That may be because in my example I indented the field with spaces, so it could be

LOAD Field1, [Fieldwith noblanks in lines] AS WhateverFROM

Field name with carriage return

Fri, 29 Oct 2010 21:04:12 GMT

Thanks again,

I let Qlikview format the load statement so I didn't have any leading spaces. I was thinking along the same lines as you so I played with the spacing but without success.

Field name with carriage return

Miguel_Angel_Baeyens — Fri, 29 Oct 2010 21:09:32 GMT

Hope that goes fine Kevin. Let us know in any case.

Field name with carriage return

Sun, 31 Oct 2010 00:47:36 GMT

I have the same problem as Kevin. I have let QlikView generate the load statement yet it still gives a 'Field not found' error on a field with a linefeed in it. Any help would be much appreciated.

Field name with carriage return

boorgura — Mon, 01 Nov 2010 16:44:30 GMT

Not sure if you found any solution for this issue?

I was trying to access a field with Carriage return in the field name from an excel file and I have no issue with it.

Can you please post a sample file?

Field name with carriage return

Mon, 01 Nov 2010 16:53:09 GMT

I finally used explicit field names to import the files as per your suggestion. I was having a problem with columns being inconsistent but I this has been fixed. I still haven't found a way of using field names with carriage returns. They work fine in objects but there is no way to reference them in script. This is an example of one of my field names:

[Master

Project ID]

Field name with carriage return

boorgura — Tue, 02 Nov 2010 15:08:49 GMT

Ok - I think I missed one of the things I wanted to ask.

When you say the field names have a carriage return, in the excel i am using,

the carriage return is placed using ALT+Enter, within the same cell.

Is your source file something different??

Field name with carriage return

Wed, 03 Nov 2010 04:15:58 GMT

Yes, the headers in question in my sheets have been created using Alt+Enter which creates the Chr(10) character (line feed) rather than Chr(13) (carriage return). I don't know if that would make a difference but given we've both used Alt+Enter I suspect not.