Loading mixed Datatypes in a column from Excel

[wonkymeister]

Hi,

i'm loading in 2 columns of data from Excel. Column A is list of questions, Column B contains values. The format of the values in column B differs from row to row.

i.e.

A	B
Headcount	12,050
FTE	10,523.25
% Required	75%
Income YTD	£12.7M

The problem i have is the way Qlik interprets the data type & format of column B - it decides the format of the data type from the first value it encounters and applies that format to the rest of the values as it loads them in.

In the above example Qlik will interpret all the column B values as numbers. I need to retain the format as it is in excel.

any ideas?

cheers,

Phil.

[awhitfield]

Hi Wonky,

Try:

LOAD Text(A) AS A,
Text(B) AS B
FROM
[Issues\Loading mixed Datatypes in a column from Excel.xlsx]
(ooxml, embedded labels, table is Sheet1);

It will load all values as text rather than assigning a datatype

Andy

[wonkymeister]

Hi Andy,

tried that and it didn't work. as in the example above QV would treat as values as a number.

[giakoum]

it looks like it is working for me

what version are you using?

[JonnyPoole]

Consider a generic load that will pivot the rows into separate columns.

You will need a key to do this, below i assigned a generic key but in real life it would be a fact based key like transaction ID or trade ID or order ID.

Generic

LOAD

  rowno() as Key,

  A as Measure,

     B as Value

FROM

[http://community.qlik.com/thread/154747]

(html, codepage is 1252, embedded labels, table is @1);

Good blog spot here on generic loads:

The Generic Load

[awhitfield · Accepted solution]

You need to make sure that the column B is formatted as text in Excel, then it works fine, see the attached

[wonkymeister]

thanks everyone - excel driving me nuts!!! issue was with excel, column format etc

got round the issue by saving my excel file as csv - and importing that.

thanks again - i should drink less coffee