Crosstable creates additional fields

[#1]

Hi All,

I have the following main table, each record has a category (I have a total of 4 categories). Then I have another table with categories as columns and then one number for each category. I need to link this to my main table so that Qlik has col1 number for category 1, col2 number for category2, etc.

Main Table:

ID	Category
1	category1
2	category2
3	category1
4	category3
5	category4
6	category4

MySecondTable:

col1	col2	col3	col4
450	740	980	239

I know I should use cross table but not sure why my script generates the categories again.

CrossTable(Category,[Category Number])

LOAD

col1 as category1,

col2 as category2,

col3 as category3,

col4 as category4

FROM MySecondTable;

[swuehl · Accepted solution]

You need at least 1 qualifying field in your CROSSTABLE LOAD:

CrossTable(Category,[Category Number])

LOAD

Recno() as CatRecno,

col1 as category1,

col2 as category2,

col3 as category3,

col4 as category4

FROM MySecondTable;

[#3]

I added Recno() as CatRecno, but it didn't work either...Do you have an example to show maybe?

[swuehl]

Do you get an error message?

This seems to work just fine for me:

LOAD * INLINE [

ID Category

1 category1

2 category2

3 category1

4 category3

5 category4

6 category4

] (delimiter is '\t');

CrossTable(Category,[Category Number])

LOAD

Recno() as CatRecno,

col1 as category1,

col2 as category2,

col3 as category3,

col4 as category4;

LOAD * INLINE [

col1 col2 col3 col4

450 740 980 239

] (delimiter is '\t');

Category	Category Number	CatRecno	ID
category1	450	1	1
category1	450	1	3
category2	740	1	2
category3	980	1	4
category4	239	1	5
category4	239	1	6

[#5]

Thanks! figured the problem with my code too!