Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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;
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;
I added Recno() as CatRecno, but it didn't work either...Do you have an example to show maybe?
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 |
Thanks! figured the problem with my code too!