Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Crosstable creates additional fields

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:

  

IDCategory
1category1
2category2
3category1
4category3
5category4
6category4

MySecondTable:

  

col1col2col3col4
450740980239

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;

1 Solution

Accepted Solutions
MVP
MVP

Re: Crosstable creates additional fields

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;

4 Replies
MVP
MVP

Re: Crosstable creates additional fields

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;

Not applicable

Re: Crosstable creates additional fields

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

MVP
MVP

Re: Crosstable creates additional fields

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
category145011
category145013
category274012
category398014
category423915
category423916
Not applicable

Re: Crosstable creates additional fields

Thanks! figured the problem with my code too!

Community Browser