Special Character "&" on a text field gets truncated

[Anonymous]

hi,

  I have an excel file of list of department.

  In the department name, there are names that has an & character in it.

  example: "Department of Public Work & Highway"

The problem when I import the file into QlikView as my data source, the departments that has "&" is truncated and displayed as

  "Highway".

  All characters before the "&" are truncated and couldn't read by QlikView.

  Any help on getting through this?

Many thanks.

[gautik92]

try removing that special character in excel and upload it

[Anonymous]

hi,

thanks for the reply but editing the excel is not an option. I just used a very simple scenario but in reality, the department field is part of an excel that gets uploaded on a daily basis with millions of records.

[nagarjuna_kotha]

Can u share sample data ?

[gautik92]

write this in ur script

Replace( yourfield,'&','')

[vishsaggi]

Hello Nald,

Can you change the excel formatting for that field as text and try. Or can you share some very less sample data so that we can try at our end.

V.

[jonathandienst]

That's not normal behaviour. Please upload your load script (or a sample qvw) and some sample data including the line that is misbehaving so that we can see what is causing this.

[jonathandienst]

[avinashelite]

Hi

Try tied the e.g with the excel it did not gave any issues for me, can you please check your excel file once ? if your still facing the issues please shares the same app and data will try to find the exact issue

[Anonymous]

Hi guys,

  Example i have an excel file named Monthly Data.xlsx.
  And its has two columns DateMonth and Department.

  In QlikView, to import the file is thru the script as shown below.

EXCEL_DATA:
LOAD

     DATEMONTH,
     DEPARTMENT
FROM
[$(Location)Monthly Data.xlsx]
(ooxml, embedded labels, table is Data);

the problem is, all department names that has a character "&" in it are automatically truncated...

so i cannot use any function like the replace or anything because the data when picked up by QlikView, its already truncated including the "&" character..

for example if a department name is "Department of Public Works & Highway",
it will be read as "Highway" only...
the rest of the characters before are truincated including the "&" character.