Use dimension as expression

[Mr_small_t]

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

[Nicole-Smith · Accepted solution]

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:

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]

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

T