topic Excel Import CrossTable with Date column headers issue in QlikView

Excel Import CrossTable with Date column headers issue

Thu, 05 Apr 2012 00:01:00 GMT

Hi.

I am running into problems when I try to import an Excel 2010 file as a CrossTable that has dates along the column headers.

Excel looks like this:

AccountID	1/11/2011	1/12/2011	1/01/2012	1/02/2012
50000	592.01	403.21	341.71	383.54
57020	12091.54	28311.87	20802	2464.86
57030	1585.65	-826.72	84.11	742.61
57310	658.14	853.25	2265.67	5696.31
57190	226.82	189.09	93.18	289.41
57010	544936.79	286851.96	270210.77	234297.24
57000	57562.58	196048.01	59365.7	162376.22
57040	56896.32	57235.5	61815.55	62512.36
57050	16393.05	65603.89	52262.09	23759.06
57060	6636.5	0	967.38	-45.23
57070	2864.14	21768.75	5600.11	18810.41
57090	1425.97	2364.82	1374.28	1613.88

Script looks like this:

CrossTable(BalMonth, Balance)
LOAD AccountID, 
     40848, 
     40878, 
     40909, 
     40940
FROM
Book2.xlsx
(ooxml, embedded labels, table is Sheet3);

And when the script runs the resultant data looks like this:

AccountID	BalMonth	Balance
50000	40848	40848
57000	40848	40848
57010	40848	40848
57020	40848	40848
57030	40848	40848
57040	40848	40848
57050	40848	40848
57060	40848	40848
57070	40848	40848
57090	40848	40848
57190	40848	40848
57310	40848	40848
50000	40878	40878
57000	40878	40878
57010	40878	40878
57020	40878	40878
57030	40878	40878
57040	40878	40878
57050	40878	40878
57060	40878	40878
57070	40878	40878
57090	40878	40878
57190	40878	40878
57310	40878	40878
50000	40909	40909
57000	40909	40909
57010	40909	40909
57020	40909	40909
57030	40909	40909
57040	40909	40909
57050	40909	40909
57060	40909	40909
57070	40909	40909
57090	40909	40909
57190	40909	40909
57310	40909	40909
50000	40940	40940
57000	40940	40940
57010	40940	40940
57020	40940	40940
57030	40940	40940
57040	40940	40940
57050	40940	40940
57060	40940	40940
57070	40940	40940
57090	40940	40940
57190	40940	40940
57310	40940	40940

The column header data is replacing the data fields.

I can get around this by having text in the column headers.

Anybody seen this?

Cheers

Adam

Excel Import CrossTable with Date column headers issue

swuehl — Thu, 05 Apr 2012 01:05:11 GMT

No, haven't seen this so far.

But I was able to reproduce your issue in QV11 IR, even with a simple LOAD ( No Crosstable LOAD). Strange.

Re: Excel Import CrossTable with Date column headers issue

Thu, 05 Apr 2012 09:38:18 GMT

Hi Adam, try with this:

tmp:
CrossTable(AccountID, Balance)
LOAD AccountID as BalMonth,
     [57020.000000],
     [57030.000000],
     [57190.000000],
     [50000.000000],
     [57060.000000],
     [57090.000000],
     [57310.000000],
     [57070.000000],
     [57050.000000],
     [57000.000000],
     [57040.000000],
     [57010.000000]
FROM
[Book2.xlsx]
(ooxml, embedded labels, table is Sheet3, filters(
Transpose()
));
data:
noconcatenate load subfield(AccountID,'.',1) as AccountID,
BalMonth,
Balance
resident tmp;
drop table tmp;

Regards

Re: Excel Import CrossTable with Date column headers issue

Bill_Britt — Tue, 03 Jul 2012 10:04:18 GMT

Adam,

It would really help if you can upload the excel file. That way it doesn't have to be recreated by anyone that might want to help you.

Bill

Re: Excel Import CrossTable with Date column headers issue

Anonymous — Fri, 27 Jul 2012 20:02:46 GMT

CrossTable(BalMonth, Balance) LOAD * FROM Book2.xlsx (ooxml, embedded labels, table is Sheet3);

have a problem with the column names that are numbers, the solution is to replace the fields with *