help:cross table

[#1]

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

[#2 · Accepted solution]

I already solved the problem,

my original load statement is

CrossTable(Month,MonthValue,6)

Load Ledger,

Dept,

Channel,

Account,

Line,

Project,

1,

2,

3,

4,

5,

6,

7,

8,

9,

10,

11,

12

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??

[adhudson]

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