topic Re: help:cross table in QlikView

help:cross table

Thu, 19 Apr 2012 01:49:13 GMT

Hi,

I have an excel file with this kind of format:

Ledger	Dept	Channel	Account	Line	Project	1	12
Ledger1	1	10	Acc01	11	Prj91	100	98
Ledger2	2	20	Acc02	12	Prj92	200	198
Ledger3	3	30	Acc03	13	Prj93	300	298
Ledger4	4	40	Acc04	14	Prj94	400	398
Ledger5	5	50	Acc05	15	Prj95	500	498

column 1 ... 12 are the month columns, when I try to use the cross table function in qlikview it gives me the wrong amount per month.

Like this

Month	Amount
1	1
2	2
3	3
4	4
5	5
6	6
7	7
8	8
9	9
10	10
11	11
12	12

Re: help:cross table

Thu, 19 Apr 2012 02:52:29 GMT

I already solved the problem,

my original load statement is

CrossTable(Month,MonthValue,6)

Load Ledger,

Dept,

Channel,

Account,

Line,

Project,

10,

11,

From Excel Source;

i enclosed the month columns in square brackets [ ] ..

CrossTable(Month,MonthValue,6)

Load Ledger,

Dept,

Channel,

Account,

Line,

Project,

[1] as Jan,

[2] as Feb,

[3] as Mar,

[4] as Apr,

[5] as May,

[6] as Jun,

[7] as Jul,

[8] as Aug,

[9] as Sep,

[10] as Oct,

[11] as Nov,

[12] as Dec

From Excel Source;

Now my question, what is the use of the square brackets??

Re: help:cross table

adhudson — Thu, 19 Apr 2012 03:55:14 GMT

Hi,

The field name could be in any format.

For instance, it can contain spaces, symbols such as slash, comma, etc..,

In your case, the field or column names are numbers.

It purely depends on the names defined in the sources.

To make it available to qlikview, i.e., to make such exceptional fields as a column, you need to include square brackets or double quotes at the start and end of the formatted column name.

In SQL, it is suggested to use double quotes to such column names.

Hope this helps.

Regards

Andrew Hudson