topic Issue with loading a crosstable from excel 2010 in QlikView

Issue with loading a crosstable from excel 2010

Mon, 23 Jul 2012 09:06:53 GMT

I am having an issue importing a 2010 excel file as a crosstable.

See attached excel file and qvw.

The excel table is as follows:

Account	Jan - 12	Feb - 12	Mar - 12	Apr - 12	May - 12	Jun- 12
110851	50		50			50
100852	50			50
100853		50			50

Qlikview is returning a date number for 'Month', and the same date number for 'Spend'.

If I use an .xls file instead it works! However this is not ideal.

Can anyone help?

Re: Issue with loading a crosstable from excel 2010

Anonymous — Mon, 23 Jul 2012 09:16:29 GMT

Hi,

This is corrected in the wizard for the latest release for v10 and I guess rolled into SR2 for v11.

Wrap the crosstable fields with brackets ([ + ]) and you'll be fine:

CrossTable(Month, Spend)

LOAD Account,

[40909],

[40940],

[40969],

[41000],

[41030],

[41061]

Re: Issue with loading a crosstable from excel 2010

Anonymous — Mon, 23 Jul 2012 09:40:50 GMT

Hi,

somehow, "Month" is recognized as a textfield.If you use

= date(trim(Month),'MMM YY') as your dimension instead of "=Month"

the table will show the correct format.

This is at least a work around.

I tested with V10 Sr 1

Re: Issue with loading a crosstable from excel 2010

Mon, 23 Jul 2012 10:14:54 GMT

Hi HRLinder,

Unfortunately the work around only fixes the Month field, not the 'Spend' field, which is what I was most concerned with.

Re: Issue with loading a crosstable from excel 2010

Anonymous — Mon, 23 Jul 2012 11:20:36 GMT

Hi,

if you use Load * or use Load Account with brackes [40909]

Directory;

CrossTable(Month, Spend)
LOAD Account

[40909],

....

from

and the workaround and you see the right values.

See attached file

Hope that helps

Re: Issue with loading a crosstable from excel 2010

vikasmahajan — Mon, 23 Jul 2012 11:23:25 GMT

I have also face this problem is in version 11 please use [ ] and load your problem will resolve.