topic Re: Loading mixed Datatypes in a column from Excel in QlikView

Loading mixed Datatypes in a column from Excel

wonkymeister — Tue, 03 Mar 2015 15:12:54 GMT

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.

Re: Loading mixed Datatypes in a column from Excel

awhitfield — Tue, 03 Mar 2015 15:21:11 GMT

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

Re: Loading mixed Datatypes in a column from Excel

wonkymeister — Tue, 03 Mar 2015 15:25:40 GMT

Hi Andy,

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

Re: Loading mixed Datatypes in a column from Excel

giakoum — Tue, 03 Mar 2015 15:32:46 GMT

it looks like it is working for me

what version are you using?

Re: Loading mixed Datatypes in a column from Excel

JonnyPoole — Tue, 03 Mar 2015 15:34:58 GMT

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

Re: Loading mixed Datatypes in a column from Excel

awhitfield — Tue, 03 Mar 2015 15:36:04 GMT

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

Re: Loading mixed Datatypes in a column from Excel

wonkymeister — Tue, 03 Mar 2015 15:40:11 GMT

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