& Character in Column Header Name

[#1]

I have an excel file I am trying to import into QlikView where the column headers contain the ampersand (&) character. I am using the wizard to try to do this import; I keep getting an error and when I debug the script, I get an error because the script now has duplicate column names.

For example, I have A&B Costs, Total A&B Costs as columns. The script truncates these to B Costs and B Costs - resulting in duplicate columns.

I do not have the ability to change the Excel file.

Is there a way to escape the & character in the data load script so that QlikView will load a file that results in two columns labelled A&B Costs and Total A&B Costs?

[Anonymous]

One quick and easy way to achieve this would be by using Script Editor. Press CTRL + E to open script editor.

Here in the Data tab below, click Table Files. This will ask you to open the excel file on your computer. Once selected you can use the Embedded Label option (as in the screenshot below) to have your column names populated.

Basically in your load script the syntax should be similar to:

LOAD [A&B Costs],

     [Total A&B Costs],

Tx

[Anonymous]

try like this

[PrashantSangle · Accepted solution]

Hi,

Not clearly understood your requirement,

but you can rename / alias  it in load to avoid duplicate column error.

for example

Load A as NewA,

A as NewB

From table;

[#5]

Thanks for responding and taking the time to provide a potential solution. I did as you suggested however the editor still refuses to recognize characters up to and including the ampersand.

So this in Excel:

Becomes this once I open the editor in QlikView and select Embedded Labels:

You can even see how the editor is appending the last field with a "1" - I presume to avoid duplicates.

The solution you proposed, unfortunately, does not resolve my problem - which is to load data with the field names as specified in Excel.

[#6]

Thank you - though I have to admit, going through and aliasing every field that contains the & is a little clunky. This did work using the script editor.