Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Mr_small_t
Contributor III
Contributor III

Use dimension as expression

Hi

For this question i use the following example table:

TEST_TABLE:
LOAD * INLINE [
id,yr,course,ind1, ind2,prov
001,2017,a,0,0,10
001,2017,a,1,0,11
001,2018,b,0,1,12
001,2019,c,0,1,13
002,2018,a,1,0,20
002,2018,a,0,0,21
002,2019,b,0,1,22
002,2020,c,0,1,23
];

Where id is an identification number, yr is year, course is a course code, ind1 and ind2 are [0,1]-indicators indicating which course was completed for level 1 and 2, and prov is the course provider. 

The output i want is a table with id in rows, yr in colomns, where the cells are populated with the course  code completed.

I use a pivot table/chart. In the attached picture there two tables, where I for simplicity have yr as rows instead of colomns. There you also can see the expressions I have written. In the first table the correct course provider and course code is listed. Then i want to use the course dimension as expression. In the second table I have tried to solve this, but course code a is not listed.

By reading posts in the Qlik-community, it seems like this can be solved by aggr. I have used aggr in several other expressions, but I have not been able to figure it out this time.

I hope someone can help me!

Regards,

Torbjørn

 

Labels (1)
1 Solution

Accepted Solutions
Nicole-Smith

I think you can get away without using aggr()...

If you set your chart up like this:
Dimension 1: id
Dimension 2: yr
Expression: only({<ind1={1}>+<ind2={1}>} course)

You end up with a table that looks like this:
screenshot.png

 

 

 

If there is any possibility of having more than one course in a year, you may want to change your expression to:
concat({<ind1={1}>+<ind2={1}>} course, ', ')

View solution in original post

2 Replies
Nicole-Smith

I think you can get away without using aggr()...

If you set your chart up like this:
Dimension 1: id
Dimension 2: yr
Expression: only({<ind1={1}>+<ind2={1}>} course)

You end up with a table that looks like this:
screenshot.png

 

 

 

If there is any possibility of having more than one course in a year, you may want to change your expression to:
concat({<ind1={1}>+<ind2={1}>} course, ', ')

Mr_small_t
Contributor III
Contributor III
Author

Thank you! Your solution works perfectly! Have a nice day 🙂

T